Forum Discussion
Progress Tracker Update via Power Query
Full disclosure, I am a complete noob to power query. Studied at Youtube University a bit but having some issues.
I am working a construction project and have built a progress tracker which is essentially a P6 schedule exported to excel. Each week I get an updated excel export from the fabricator which indicates their completion percentages for each schedule activity. I am maintaining my own estimated completion percentage for comparison.
I attempted to create a power query that updates my file, which includes my %, with the fabricators file as data. The query is linked to a folder where my intent is to dump the new file each week and then refresh the query.
When I created the power query, I had three files in the data folder and it seemed to function the way I wanted, but, today, I dumped the new file for this week and refreshed the query. It seemed to refresh without error but the percentages do not appear to have updated.
Any ideas on what could be at issue here? I considered that power query might not know which file is the most recent file as I do not understand how it determines that. Does it look at date created or date modified? The file names of the files in the data folder contain the date. Can I use that to specify most recent?
Any help on this would be appreciated. Thanks in advance.
10 Replies
I considered that power query might not know which file is the most recent fileExactly. Power Query does not guarantee a sort order. You will need to bring your own sort order. In your case you would add a step that sorts by file creation date descending, and then grabs the data from the first file (row 0).
Seems simple enough but I cannot figure out how to implement. I created a new spreadsheet and recreated the power query beginning with a sort on the source files by date created. Now it adds rows from each file sequentially rather than updating the rows from the first file with each updated file.
Any tips on that?
rather than updating the rows from the first file with each updated file.eh? I may have misunderstood what you are ultimately trying to achieve. Do you want to ingest only the latest file, or do you expect to be able to modify existing dataset data with information from the latest file?
Power BI has no memory* . You need to manage your snapshots in the upstream system.
* Yes, there is the self referencing option but that has no safety net.
When you use From Folder in power query, any the three field can be used for sorting or filtering
