How to add data to an existing Power Query

[cmstoren]

Hey - this seems like it should be very simple to do but I'm still struggling! Each week I get a report as a csv file with has lots of superfluous information and merged cells. I've used power query to sort it out the way I want, but can't seem to find a way to add the next weeks csv file without going through the whole process again. Any help would be greatly appreciated! (I can use either windows or mac as I know the mac has a few less features). - Chloe

 

[alansidman]

Why not copy your Mcode from the previous week and paste into the current week.

 

[cmstoren]

Thanks for the reply - I can seem to do it when using my pc but I guess it's just operator failure on the mac. I'm happy to just use the pc at the moment

 

[GraH]

Hey - this seems like it should be very simple to do but I'm still struggling! Each week I get a report as a csv file with has lots of superfluous information and merged cells. I've used power query to sort it out the way I want, but can't seem to find a way to add the next weeks csv file without going through the whole process again. Any help would be greatly appreciated! (I can use either windows or mac as I know the mac has a few less features). - Chloe

Turn your query into a function.
Read from folder, and apply the function to each .csv.
But how can a csv, contain "merged cells"? It's a text file format.

You can actually do the basic set-up, starting all over, where you connect to the folder, use the first file as sample and combine the data.
PQ will create a sample query, a parameter and a function based on the sample query.
Update the sample query with the code you have.
Should get the job done.

When you do not want to redo the previous file, the alternative is to apply the same, but use a filter to only load the latest csv. Then save as file a, b, c, ... as days go by. Basically you create file x as your template.

 

[Automatrix]

I usually connect to an import folder, sort by date created (descending) to get the newest file to be on top, and then select it with

#"First file" = #"Sorted Rows"{0}[Content]

This way the query will automatically work with the freshest file in the import folder.

 

[GraH]

I usually connect to an import folder, sort by date created (descending) to get the newest file to be on top, and then select it with

#"First file" = #"Sorted Rows"{0}[Content]

This way the query will automatically work with the freshest file in the import folder.

Potentially this is a dangerous approach, as sorting is not always sorting in PQ. While I assume the numbers of files is always a low number, and there is never going to be an issue, I have seen cases where the sorting is not "remembered" in the following steps even on a small data set.

The technique I referred too never needs any sorting, and it's doable with the UI, so available for everybody.