Forum Discussion
Solved
Appending query to itself, keeping previous rows
I have a power Query set up which pulls data from Sharepoint List. I do my transformations there and end up with 4 rows, one of which is a column I created, today's date. This loads to Excel. How c...
Imagine the weekly update is like in the blue table (screenshot below) and the combined one from earlier updates is the green one and comes from an earlier run of the query
The following M-code will connect to tblSource (Source) AND tblPQ (History) and then append Source to History to create an extended tblPQ.
let Source = Excel.CurrentWorkbook(){[Name="tblSource"]}[Content], History = Excel.CurrentWorkbook(){[Name="tblPQ"]}[Content], #"Appended Query" = Table.Combine({History, Source}), #"Changed Type" = Table.TransformColumnTypes(#"Appended Query",{{"Snap", type date}, {"ID", type text}}) in #"Changed Type"It results in this:
and a next run may look like this:
etc.
The only way to acheive that in Power Query and to keep the data, is to created a new table for each refresh
But if you need to update a table or if someone refresh the table, you will lose all snapshots
My suggestion is to built something like this, would be to use Fabric or you can also do that with Power Automate and dataverse