Excel Forum

Excel Template File That Uses Power Query

Rick Waffird

New Member

Members

Forum Posts: 2

Member Since:
February 14, 2019

Offline

February 14, 2019 - 3:44 am

Hello,

Any assistance with this effort would be amazing.

We run a daily data file from our financial system and then post it with various pivots. They rebuild the pivots every day and save that file for historical record using the date in the filename.

I'm suggesting we setup a macro-enabled template file with all of the pivots and reference tables in a data model, as well as a power query connection to a data file that changes daily, stored in the same location as the previous days' data files. After uploading the new data file, the user would then browse to the new data file to update the Power Query formulas, changing the connection to new data file(s). The PQ would combine the files into one table and perform the transforms we need for our reporting.

I need each saved file to maintain it's connection to the corresponding data file on that date.

Thanks so much!

Rick

Mynda Treacy

Admin

Forum Posts: 4449

Member Since:
July 16, 2010

Offline

February 14, 2019 - 9:39 am

Sounds like a good plan, Rick. Set up your first Excel file that uses Power Query to get the source data from the day1 file and load it into Power Pivot. Do your analysis in PivotTables etc. in the same Excel file.

Then when the day2 file is available make a copy of the Excel file containing your Power Query query and Power Pivot model etc. Edit the query to point at the day2 source file. Refresh All and you're done.

Rinse and repeat for day3 and so on.

Mynda

Rick Waffird

New Member

Members

Forum Posts: 2

Member Since:
February 14, 2019

Offline

February 14, 2019 - 11:08 pm

Thanks so much Mynda!

Will a Template file (*.xltm) work? I also want the user to locate/browse to get the new data file - much like the old Get Data function worked prompting the user for the file. I've not been able to reproduce that function in the new PQ methods.

Mynda Treacy

Admin

Forum Posts: 4449

Member Since:
July 16, 2010

Offline

February 15, 2019 - 8:52 am

Hi Rick,

TBH I've not tried Power Query with a .xltm file. You could give it a go.

For easy updating of the source file, you can use a parameter table in the Excel worksheet that the user can populate the file path with and then they just click 'Refresh All'. I teach this in my Power Query course.

Mynda