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

Click to Download Project 2 answered Folder

Folder contains

1) File named "Dash Board.Xls " has about 8 sheets all hidden to maximize the view area for dashboard.

2) Deliverables macrp enabled worksheet with 3 macros one by Record Macro function (Formating a Quarterly Sales Details), twp from VBA procedures 1. ConverToPdf 2. MOd_SpellCheck'

3) Additional Workbook with summary tables of all Female / Male client's Income and Loan amount using Table functions also includes summary of all flag based fields.

4) Multiple pdf files created while making the module to work.

Though it was a exciting and spledid experience creating my first DASHBOARD it was very tiring. May be because first time it took enormus time but at the end it is satisfying ( Need more time to create some more charts)

First Data set was loaded and Table was created and named.

Then using powerQuery

Data > Get & Transform Tab > From Table ( make sure a cell of the table is highlighted) -> This opens the powerquery. In Queries window Double click on the Table.. and give the name of choice. A sample table with fields being assigned data types in Green shaded table is displayed. On the right end is Query settings window displays properties and Applied Steps.As we transform each step is listed and anytime deleting in reverse order shall UNDO the mthgods execured.

A have quick and Good look of the fields and data values. THe current set has 307512 records and 122 fields.Some of the fields with 'null "values were identified appropriately changed using Home > Transform > Replace Values.

Then Flag columns could have been changed to TRUE / FALSE or keep same with 1 / 0 as ordinal values.Fields like AMT_INCOME_TOTAL, AMT_CREDIT etc by defeault assigned Number are changed to Currency.

Proceed to Transform > Table >Use First Row as Headers.

Once done Home > Query > Close & Load to return to excel

now create the pivot table / Pivot chars as required.