Progress Tracker Update via Power Query

[herbally]

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.

[Omid_Motamedise]

When you use From Folder in power query, any the three field can be used for sorting or filtering

[lbendlin]

 I considered that power query might not know which file is the most recent file

Exactly.  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).

[herbally]

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?

[lbendlin]

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.

[herbally]

Every week I get an updated file with a new updated completion percentage for every activity. I thought I could use power query to automatically pull that new percentage from the latest file and update my spreadsheet so that I could just throw each new file into the data source folder each week.

Does that make it any clearer?

[lbendlin]

and update my spreadsheet

Wait what - are you doing this in Excel Power Query or in Power BI Power Query?

[herbally]

Excel > Get Data > From Folder > Combine and Transform

My limited understanding is that creates a power query used to manipulate data in my excel spreadsheet.

[lbendlin]

yeah, but that auto generated power Query is inefficient, and should only be used by novices.  Evaluate how they did it, then throw it away and build your own.

[herbally]

Novice might be overstating my power query skill level. Is there a good resource that you can point me to where I might be able to learn more about it?

Seems the videos I find all just walk you through the process described above which you said is not very good.

[lbendlin]

By far the best resource about Power Query is Ben Gribaudo's Primer.  https://bengribaudo.com/blog/2017/11/17/4107/power-query-m-primer-part1-introduction-simple-expressi...

I am planning on a blog entry about that ingestion in the near future. For the time being keep learning by evaluating their Power Query code and trying to see "how they did it", piece by piece. 

If you like you can submit two (or more) sample files and indicate the expected result.

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...