Power query from folder....but report format has just been altered. now what?

[Guitarmageddon]

So I had success nailing down some measures last week in a pretty basic query "from folder" on a report that is basically a summary of daily purchase orders. We dump the daily file in there and I have queried/combined all those into one.

However now, after over halfway through the year being done and 200ish files in the folder being queried, we have added a couple data points to the report going forward and included 3 new columns.

How can this sort of scenario be managed without breaking the work I've already done? The old query and all its columns of the report would still be relevant, but now we have added 3 new columns referencing dates. I'm basically just putting the output of this query into a flat pivot table, and the planners that use the output filter to whatever information they want. I would be fine with those three new columns showing up in the query and anything prior to the current date, where the change was executed, just showing blanks. Thoughts? thanks for any help.

 

[Alex Blakenburg]

Are you loading all 200+ files in at the one time ?
Do the new files have the same headings apart from having 3 additional columns ?

Assuming you answer yes to the above, then PQ will have selected 1 of the files to use as a "Transform Sample File" most likely in a PQ folder called Helper Queries.

• Under Helper Queries open the item that says Sample File
• Click on the Source step and apply a filter that will return only a file or files that have the additional 3 headings.
• If you now go to Transform Sample Files you see the 1st file in that filtered list appear and the additional columns should appear.
• Now go to your final query and see if it still works. If you haven't done anything fancy the only thing you might have to do is fix the last Changed Type step to fix the additional 3 column data types.

If you get errors then backtrack through your steps until you get to the step just before you start getting errors.
But come back to us if it comes to that, it sounds like your query might be fairly straight forward and the above will just work.

 

[Guitarmageddon]

Are you loading all 200+ files in at the one time ?
Do the new files have the same headings apart from having 3 additional columns ?

Assuming you answer yes to the above, then PQ will have selected 1 of the files to use as a "Transform Sample File" most likely in a PQ folder called Helper Queries.

• Under Helper Queries open the item that says Sample File
• Click on the Source step and apply a filter that will return only a file or files that have the additional 3 headings.
• If you now go to Transform Sample Files you see the 1st file in that filtered list appear and the additional columns should appear.
• Now go to your final query and see if it still works. If you haven't done anything fancy the only thing you might have to do is fix the last Changed Type step to fix the additional 3 column data types.

If you get errors then backtrack through your steps until you get to the step just before you start getting errors.
But come back to us if it comes to that, it sounds like your query might be fairly straight forward and the above will just work.

Revisiting this one. So in my source folder, there are files going back a few years. I had the query do a filter on just this fiscal year. So Feb 1 onwards. It was using that 2-1-21 file as the sample file.

I am not able to pick any other file?

 

[Guitarmageddon]

So since my initial query is sorting the source files by date created, its choosing the oldest date, and thus first result, no matter what. Not sure how to get around this.

 

[Guitarmageddon]

...I think I may have gotten around by just sorting the source files by date added, newest to oldest, thus making the sample file be the latest file (the one with the newer added columns)

 

[Alex Blakenburg]

I think that is a good option. It should solve the issue.