Data Transformation using PowerQuery and/or Macros

[hpatel517]

Hello,

I'm looking to populate a series of dashboards using PowerQuery and/or Macros.

I receive a new file every day with updated data. These files come in the same format each day with the previous day's data added and corrections to days prior as necessary. Each file will encompass 1 pay period (14 days). My plan is to overwrite the file received each day for that pay period. This data is represented in the attached Sample Daily File 1 and 2.

What I'm looking to set up is an automated way to pull the data into the Dashboard (SUM of the respective column by date). I would like to have the ability to flip the cost center in cell B1 of each Dashboard and the appropriate data would populate for each day based on that selection (the cost centers are listed in Column A of the Sample Daily File). Each Dashboard tab represents a new pay period that also aligns to the Daily File names. There will be days that are blank (if we don't have the data for it yet.

Some constraints to keep in mind:

• I'd like to avoid, as much as possible, full data sets being repasted into the Dashboard workbook to minimize the size of the file
• I'd like to keep all manipulation in the dashboard file. The Sample Daily Files should only serve as the data source
• The Set-Up tab (optional) can be used to house a Button to enable a macro, or paste file paths, etc.

I hope this information is helpful. I've also included a Sample Dashboard_Completed file that shows the intended output. This is just for demonstrative purposes only and doesn't necessarily adhere to all of the constraints I mentioned above.

Thank you so much in advance for your help!