Save a version of a query for future referance - not working

[Nandor]

A price that is loaded in a table. The objective is to save a version so that I can compare it in the future with the saved version to identify any price changes (Excel Power query)

Price table:

Item name       Price

Item 1	101
Item 2	102
Item 3	103
Item 4	104
Item 5	105
Item 6	106
Item 7	107
Item 8	108
Item 9	109
Item 10	110

When the prices change, I need a table to show both the original prices and the new prices. The final version should look like:

Item name	Price (New)	Reference price (original)	Refresh
Item 1	200	101	19/06/2021 ; 22:59:45
Item 2	202	102	19/06/2021 ; 22:59:45
Item 3	204	103	19/06/2021 ; 22:59:45
Item 4	206	104	19/06/2021 ; 22:59:45
Item 5	208	105	19/06/2021 ; 22:59:45
Item 6	210	106	19/06/2021 ; 22:59:45
Item 7	212	107	19/06/2021 ; 22:59:45
Item 8	214	108	19/06/2021 ; 22:59:45
Item 9	216	109	19/06/2021 ; 22:59:45
Item 10	218	110	19/06/2021 ; 22:59:45

I aproached the following strategy:

1. I loaded the price table in a Query then I made a duplicate

2. I modified the propreties of the duplicate table and unticked the "Refres this connection on Refresh All"

3. In the next step I merged the two tables and brought together booth columns the one from the main table and the reference column from the duplicate table.

The problem I face is the following:

The duplicate table is working correctly, it refreshes only when I refres the table manually

The merged table is not woking correctly, it is not bringing in the values from the Reference table, instead it bring the new values (Prices). The same with the refresh column.

Item name	Price (New)	Reference price (original)	Refresh
Item 1	200	200	19/06/2021 ; 22:59:45
Item 2	202	202	19/06/2021 ; 22:59:45
Item 3	204	204	19/06/2021 ; 22:59:45
Item 4	206	206	19/06/2021 ; 22:59:45
Item 5	208	208	19/06/2021 ; 22:59:45
Item 6	210	210	19/06/2021 ; 22:59:45
Item 7	212	212	19/06/2021 ; 22:59:45
Item 8	214	214	19/06/2021 ; 22:59:45
Item 9	216	216	19/06/2021 ; 22:59:45
Item 10	218	218	19/06/2021 ; 22:59:45

I

The input table is the format I posted above (Price table).

The reference table:

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Item name", type text}, {"Price", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
#"Renamed Columns" = Table.RenameColumns(#"Added Index",{{"Price", "Price referance"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "Refresh", each DateTime.LocalNow()),
#"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Index", "Item name", "Price referance", "Refresh"})
in
#"Reordered Columns"

The final output table:

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Item name", type text}, {"Price", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
#"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "Item name", "Price"}),
#"Merged Queries" = Table.NestedJoin(#"Reordered Columns", {"Index"}, #"Table1 Referance", {"Index"}, "Table1 Referance", JoinKind.LeftOuter),
#"Expanded Table1 Referance" = Table.ExpandTableColumn(#"Merged Queries", "Table1 Referance", {"Price referance", "Refresh"}, {"Table1 Referance.Price referance", "Table1 Referance.Refresh"})
in
#"Expanded Table1 Referance"

Thank you in advance.

[v-yingjl · Accepted solution]

Hi @Nandor ,

When duplicating a table in excel, it would actually produce two tables with the source table, one is called Table1_2 and another is called Table1_Reference(the source table is Table1).

So actually in excel power query editor, the real 'Table 1' table should be ‘Table1_2’ instead of 'Table1'.

Change your refernce table query as:

= Excel.CurrentWorkbook(){[Name="Table1_2"]}[Content]

 Now it would show the merge result correctly.

If both tables refer 'Table 1' which is the source table, they would be change by it at the same time when the source table values change.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
 

[Nandor]

Dear Yingjl,

I just realised that my example does not reproduce my original project. In my project I am not referencing only one table, I am referencing several tables.

In reality I have several stores to gether data from and I am appending the data in one query:

let
Source = Excel.CurrentWorkbook(),
#"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Name], "Store")),
#"Expanded Content" = Table.ExpandTableColumn(#"Filtered Rows", "Content", {"Item name", "Price"}, {"Item name", "Price"})
in
#"Expanded Content"

Please see a dropbox link to the file: https://www.dropbox.com/s/0j7khi6y3i8zagm/Sample.xlsx?dl=0

These are the two source tables: Prices_Store1 and Prices_Store1

Please help me to modify the code for this case.

Thank you,

Best regards

[Nandor]

Thank you, it's working!