Project 2 - Create a report using PowerQuery, Macro VBA, List Functions and Data Validation functions for Data Reporting of Supply Chain Management companies

Sub convert data to pdf

'

'

ActiveWorkbook.Queries.Add Name:="Applicant Data", Formula:= _

"let" & Chr(13) & "" & Chr(10) & " Source = Excel.Workbook(File.Contents(""C:\Users\Priya\Downloads\Applicant Data1.xlsx""), null, true)," & Chr(13) & "" & Chr(10) & " #""Applicant Data_Sheet"" = Source{[Item=""Applicant Data"",Kind=""Sheet""]}[Data]," & Chr(13) & "" & Chr(10) & " #""Promoted Headers"" = Table.PromoteHeaders(#""Applicant Data_Sheet"", [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(#""Promoted" & _

" Headers"",{{""SK_ID_CURR"", Int64.Type}, {""TARGET"", Int64.Type}, {""NAME_CONTRACT_TYPE"", type text}, {""CODE_GENDER"", type text}, {""FLAG_OWN_CAR"", type text}, {""FLAG_OWN_REALTY"", type text}, {""CNT_CHILDREN"", Int64.Type}, {""AMT_INCOME_TOTAL"", type number}, {""AMT_CREDIT"", type number}, {""AMT_ANNUITY"", type number}, {""AMT_GOODS_PRICE"", Int64.Type}, {" & _

"""NAME_TYPE_SUITE"", type text}, {""NAME_INCOME_TYPE"", type text}, {""NAME_EDUCATION_TYPE"", type text}, {""NAME_FAMILY_STATUS"", type text}, {""NAME_HOUSING_TYPE"", type text}, {""REGION_POPULATION_RELATIVE"", type number}, {""DAYS_BIRTH"", Int64.Type}, {""DAYS_EMPLOYED"", Int64.Type}, {""DAYS_REGISTRATION"", Int64.Type}, {""DAYS_ID_PUBLISH"", Int64.Type}, {""OWN_" & _

"CAR_AGE"", Int64.Type}, {""FLAG_MOBIL"", Int64.Type}, {""FLAG_EMP_PHONE"", Int64.Type}, {""FLAG_WORK_PHONE"", Int64.Type}, {""FLAG_CONT_MOBILE"", Int64.Type}, {""FLAG_PHONE"", Int64.Type}, {""FLAG_EMAIL"", Int64.Type}, {""OCCUPATION_TYPE"", type text}, {""CNT_FAM_MEMBERS"", Int64.Type}, {""REGION_RATING_CLIENT"", Int64.Type}, {""REGION_RATING_CLIENT_W_CITY"", Int64." & _

"Type}, {""WEEKDAY_APPR_PROCESS_START"", type text}, {""HOUR_APPR_PROCESS_START"", Int64.Type}, {""REG_REGION_NOT_LIVE_REGION"", Int64.Type}, {""REG_REGION_NOT_WORK_REGION"", Int64.Type}, {""LIVE_REGION_NOT_WORK_REGION"", Int64.Type}, {""REG_CITY_NOT_LIVE_CITY"", Int64.Type}, {""REG_CITY_NOT_WORK_CITY"", Int64.Type}, {""LIVE_CITY_NOT_WORK_CITY"", Int64.Type}, {""ORGA" & _

"NIZATION_TYPE"", type text}, {""EXT_SOURCE_1"", type number}, {""EXT_SOURCE_2"", type number}, {""EXT_SOURCE_3"", type number}, {""APARTMENTS_AVG"", type number}, {""BASEMENTAREA_AVG"", type number}, {""YEARS_BEGINEXPLUATATION_AVG"", type number}, {""YEARS_BUILD_AVG"", type number}, {""COMMONAREA_AVG"", type number}, {""ELEVATORS_AVG"", type number}, {""ENTRANCES_AV" & _

"G"", type number}, {""FLOORSMAX_AVG"", type number}, {""FLOORSMIN_AVG"", type number}, {""LANDAREA_AVG"", type number}, {""LIVINGAPARTMENTS_AVG"", type number}, {""LIVINGAREA_AVG"", type number}, {""NONLIVINGAPARTMENTS_AVG"", type number}, {""NONLIVINGAREA_AVG"", type number}, {""APARTMENTS_MODE"", type number}, {""BASEMENTAREA_MODE"", type number}, {""YEARS_BEGINEX" & _

"PLUATATION_MODE"", type number}, {""YEARS_BUILD_MODE"", type number}, {""COMMONAREA_MODE"", type number}, {""ELEVATORS_MODE"", type number}, {""ENTRANCES_MODE"", type number}, {""FLOORSMAX_MODE"", type number}, {""FLOORSMIN_MODE"", type number}, {""LANDAREA_MODE"", type number}, {""LIVINGAPARTMENTS_MODE"", type number}, {""LIVINGAREA_MODE"", type number}, {""NONLIVI" & _

"NGAPARTMENTS_MODE"", type number}, {""NONLIVINGAREA_MODE"", type number}, {""APARTMENTS_MEDI"", type number}, {""BASEMENTAREA_MEDI"", type number}, {""YEARS_BEGINEXPLUATATION_MEDI"", type number}, {""YEARS_BUILD_MEDI"", type number}, {""COMMONAREA_MEDI"", type number}, {""ELEVATORS_MEDI"", type number}, {""ENTRANCES_MEDI"", type number}, {""FLOORSMAX_MEDI"", type nu" & _

"mber}, {""FLOORSMIN_MEDI"", type number}, {""LANDAREA_MEDI"", type number}, {""LIVINGAPARTMENTS_MEDI"", type number}, {""LIVINGAREA_MEDI"", type number}, {""NONLIVINGAPARTMENTS_MEDI"", type number}, {""NONLIVINGAREA_MEDI"", type number}, {""FONDKAPREMONT_MODE"", type text}, {""HOUSETYPE_MODE"", type text}, {""TOTALAREA_MODE"", type number}, {""WALLSMATERIAL_MODE"", " & _

"type text}, {""EMERGENCYSTATE_MODE"", type text}, {""OBS_30_CNT_SOCIAL_CIRCLE"", Int64.Type}, {""DEF_30_CNT_SOCIAL_CIRCLE"", Int64.Type}, {""OBS_60_CNT_SOCIAL_CIRCLE"", Int64.Type}, {""DEF_60_CNT_SOCIAL_CIRCLE"", Int64.Type}, {""DAYS_LAST_PHONE_CHANGE"", Int64.Type}, {""FLAG_DOCUMENT_2"", Int64.Type}, {""FLAG_DOCUMENT_3"", Int64.Type}, {""FLAG_DOCUMENT_4"", Int64.Ty" & _

"pe}, {""FLAG_DOCUMENT_5"", Int64.Type}, {""FLAG_DOCUMENT_6"", Int64.Type}, {""FLAG_DOCUMENT_7"", Int64.Type}, {""FLAG_DOCUMENT_8"", Int64.Type}, {""FLAG_DOCUMENT_9"", Int64.Type}, {""FLAG_DOCUMENT_10"", Int64.Type}, {""FLAG_DOCUMENT_11"", Int64.Type}, {""FLAG_DOCUMENT_12"", Int64.Type}, {""FLAG_DOCUMENT_13"", Int64.Type}, {""FLAG_DOCUMENT_14"", Int64.Type}, {""FLAG_" & _

"DOCUMENT_15"", Int64.Type}, {""FLAG_DOCUMENT_16"", Int64.Type}, {""FLAG_DOCUMENT_17"", Int64.Type}, {""FLAG_DOCUMENT_18"", Int64.Type}, {""FLAG_DOCUMENT_19"", Int64.Type}, {""FLAG_DOCUMENT_20"", Int64.Type}, {""FLAG_DOCUMENT_21"", Int64.Type}, {""AMT_REQ_CREDIT_BUREAU_HOUR"", Int64.Type}, {""AMT_REQ_CREDIT_BUREAU_DAY"", Int64.Type}, {""AMT_REQ_CREDIT_BUREAU_WEEK"", " & _

"Int64.Type}, {""AMT_REQ_CREDIT_BUREAU_MON"", Int64.Type}, {""AMT_REQ_CREDIT_BUREAU_QRT"", Int64.Type}, {""AMT_REQ_CREDIT_BUREAU_YEAR"", Int64.Type}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type"""

ActiveWorkbook.Queries.Add Name:="Client’s Housing Type", Formula:= _

"let" & Chr(13) & "" & Chr(10) & " Source = Excel.Workbook(File.Contents(""C:\Users\Priya\Downloads\Applicant Data1.xlsx""), null, true)," & Chr(13) & "" & Chr(10) & " #""Client’s Housing Type_Sheet"" = Source{[Item=""Client’s Housing Type"",Kind=""Sheet""]}[Data]," & Chr(13) & "" & Chr(10) & " #""Promoted Headers"" = Table.PromoteHeaders(#""Client’s Housing Type_Sheet"", [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformCo" & _

"lumnTypes(#""Promoted Headers"",{{""Row Labels"", type text}, {""Count of NAME_HOUSING_TYPE"", Int64.Type}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type"""

ActiveWorkbook.Queries.Add Name:="Gender Ratio", Formula:= _

"let" & Chr(13) & "" & Chr(10) & " Source = Excel.Workbook(File.Contents(""C:\Users\Priya\Downloads\Applicant Data1.xlsx""), null, true)," & Chr(13) & "" & Chr(10) & " #""Gender Ratio_Sheet"" = Source{[Item=""Gender Ratio"",Kind=""Sheet""]}[Data]," & Chr(13) & "" & Chr(10) & " #""Promoted Headers"" = Table.PromoteHeaders(#""Gender Ratio_Sheet"", [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Heade" & _

"rs"",{{""Row Labels"", type text}, {""Count of CODE_GENDER"", Int64.Type}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type"""

ActiveWorkbook.Queries.Add Name:="House Owner Non-Owner Ratio", Formula:= _

"let" & Chr(13) & "" & Chr(10) & " Source = Excel.Workbook(File.Contents(""C:\Users\Priya\Downloads\Applicant Data1.xlsx""), null, true)," & Chr(13) & "" & Chr(10) & " #""House Owner Non-Owner Ratio_Sheet"" = Source{[Item=""House Owner Non-Owner Ratio"",Kind=""Sheet""]}[Data]," & Chr(13) & "" & Chr(10) & " #""Promoted Headers"" = Table.PromoteHeaders(#""House Owner Non-Owner Ratio_Sheet"", [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & " #""Changed Type" & _

""" = Table.TransformColumnTypes(#""Promoted Headers"",{{""Row Labels"", type text}, {""Count of HOUSETYPE_MODE"", Int64.Type}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type"""

ActiveWorkbook.Queries.Add Name:="Client Income Type", Formula:= _

"let" & Chr(13) & "" & Chr(10) & " Source = Excel.Workbook(File.Contents(""C:\Users\Priya\Downloads\Applicant Data1.xlsx""), null, true)," & Chr(13) & "" & Chr(10) & " #""Client Income Type_Sheet"" = Source{[Item=""Client Income Type"",Kind=""Sheet""]}[Data]," & Chr(13) & "" & Chr(10) & " #""Promoted Headers"" = Table.PromoteHeaders(#""Client Income Type_Sheet"", [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes" & _

"(#""Promoted Headers"",{{""Row Labels"", type text}, {""Count of NAME_INCOME_TYPE"", Int64.Type}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type"""

ActiveWorkbook.Queries.Add Name:="Client’s Education", Formula:= _

"let" & Chr(13) & "" & Chr(10) & " Source = Excel.Workbook(File.Contents(""C:\Users\Priya\Downloads\Applicant Data1.xlsx""), null, true)," & Chr(13) & "" & Chr(10) & " #""Client’s Education_Sheet"" = Source{[Item=""Client’s Education"",Kind=""Sheet""]}[Data]," & Chr(13) & "" & Chr(10) & " #""Promoted Headers"" = Table.PromoteHeaders(#""Client’s Education_Sheet"", [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes" & _

"(#""Promoted Headers"",{{""Row Labels"", type text}, {""Count of NAME_EDUCATION_TYPE"", Int64.Type}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type"""

ActiveWorkbook.Queries.Add Name:="Client’s Family Status", Formula:= _

"let" & Chr(13) & "" & Chr(10) & " Source = Excel.Workbook(File.Contents(""C:\Users\Priya\Downloads\Applicant Data1.xlsx""), null, true)," & Chr(13) & "" & Chr(10) & " #""Client’s Family Status_Sheet"" = Source{[Item=""Client’s Family Status"",Kind=""Sheet""]}[Data]," & Chr(13) & "" & Chr(10) & " #""Promoted Headers"" = Table.PromoteHeaders(#""Client’s Family Status_Sheet"", [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.Transfor" & _

"mColumnTypes(#""Promoted Headers"",{{""Row Labels"", type text}, {""Count of NAME_FAMILY_STATUS"", Int64.Type}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type"""

ActiveWorkbook.Queries.Add Name:="Client’s Occupation Type", Formula:= _

"let" & Chr(13) & "" & Chr(10) & " Source = Excel.Workbook(File.Contents(""C:\Users\Priya\Downloads\Applicant Data1.xlsx""), null, true)," & Chr(13) & "" & Chr(10) & " #""Client’s Occupation Type_Sheet"" = Source{[Item=""Client’s Occupation Type"",Kind=""Sheet""]}[Data]," & Chr(13) & "" & Chr(10) & " #""Promoted Headers"" = Table.PromoteHeaders(#""Client’s Occupation Type_Sheet"", [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.Tr" & _

"ansformColumnTypes(#""Promoted Headers"",{{""Row Labels"", type text}, {""Count of OCCUPATION_TYPE"", Int64.Type}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type"""

ActiveWorkbook.Queries.Add Name:="Owner Non-Owner Ratio", Formula:= _

"let" & Chr(13) & "" & Chr(10) & " Source = Excel.Workbook(File.Contents(""C:\Users\Priya\Downloads\Applicant Data1.xlsx""), null, true)," & Chr(13) & "" & Chr(10) & " #""Owner Non-Owner Ratio_Sheet"" = Source{[Item=""Owner Non-Owner Ratio"",Kind=""Sheet""]}[Data]," & Chr(13) & "" & Chr(10) & " #""Promoted Headers"" = Table.PromoteHeaders(#""Owner Non-Owner Ratio_Sheet"", [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.Transfor" & _

"mColumnTypes(#""Promoted Headers"",{{""Row Labels"", type text}, {""Count of FLAG_OWN_CAR"", Int64.Type}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type"""

Workbooks("Book1").Connections.Add2 "Query - Applicant Data", _

"Connection to the 'Applicant Data' query in the workbook.", _

"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Applicant Data;Extended Properties=" _

, """Applicant Data""", 6, True, False

Workbooks("Book1").Connections.Add2 "Query - Client’s Housing Type", _

"Connection to the 'Client’s Housing Type' query in the workbook.", _

"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Client’s Housing Type;Extended Properties=" _

, """Client’s Housing Type""", 6, True, False

Workbooks("Book1").Connections.Add2 "Query - Gender Ratio", _

"Connection to the 'Gender Ratio' query in the workbook.", _

"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Gender Ratio;Extended Properties=" _

, """Gender Ratio""", 6, True, False

Workbooks("Book1").Connections.Add2 "Query - House Owner Non-Owner Ratio", _

"Connection to the 'House Owner Non-Owner Ratio' query in the workbook.", _

"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=House Owner Non-Owner Ratio;Extended Properties=" _

, """House Owner Non-Owner Ratio""", 6, True, False

Workbooks("Book1").Connections.Add2 "Query - Client Income Type", _

"Connection to the 'Client Income Type' query in the workbook.", _

"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Client Income Type;Extended Properties=" _

, """Client Income Type""", 6, True, False

Workbooks("Book1").Connections.Add2 "Query - Client’s Education", _

"Connection to the 'Client’s Education' query in the workbook.", _

"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Client’s Education;Extended Properties=" _

, """Client’s Education""", 6, True, False

Workbooks("Book1").Connections.Add2 "Query - Client’s Family Status", _

"Connection to the 'Client’s Family Status' query in the workbook.", _

"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Client’s Family Status;Extended Properties=" _

, """Client’s Family Status""", 6, True, False

Workbooks("Book1").Connections.Add2 "Query - Client’s Occupation Type", _

"Connection to the 'Client’s Occupation Type' query in the workbook.", _

"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Client’s Occupation Type;Extended Properties=" _

, """Client’s Occupation Type""", 6, True, False

Workbooks("Book1").Connections.Add2 "Query - Owner Non-Owner Ratio", _

"Connection to the 'Owner Non-Owner Ratio' query in the workbook.", _

"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Owner Non-Owner Ratio;Extended Properties=" _

, """Owner Non-Owner Ratio""", 6, True, False

End Sub