cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Nandor
Helper I
Helper I

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

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 1101
Item 2102
Item 3103
Item 4104
Item 5105
Item 6106
Item 7107
Item 8108
Item 9109
Item 10110

 

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 namePrice (New)Reference price (original)Refresh
Item 120010119/06/2021 ; 22:59:45
Item 220210219/06/2021 ; 22:59:45
Item 320410319/06/2021 ; 22:59:45
Item 420610419/06/2021 ; 22:59:45
Item 520810519/06/2021 ; 22:59:45
Item 621010619/06/2021 ; 22:59:45
Item 721210719/06/2021 ; 22:59:45
Item 821410819/06/2021 ; 22:59:45
Item 921610919/06/2021 ; 22:59:45
Item 1021811019/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"

Nandor_0-1624133998517.png

 

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 namePrice (New)Reference price (original)Refresh
Item 120020019/06/2021 ; 22:59:45
Item 220220219/06/2021 ; 22:59:45
Item 320420419/06/2021 ; 22:59:45
Item 420620619/06/2021 ; 22:59:45
Item 520820819/06/2021 ; 22:59:45
Item 621021019/06/2021 ; 22:59:45
Item 721221219/06/2021 ; 22:59:45
Item 821421419/06/2021 ; 22:59:45
Item 921621619/06/2021 ; 22:59:45
Item 1021821819/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.

1 ACCEPTED SOLUTION
v-yingjl
Community Support
Community Support

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.

Screenshot 2021-06-22 095226.png

 

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.
 

View solution in original post

3 REPLIES 3
v-yingjl
Community Support
Community Support

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.

Screenshot 2021-06-22 095226.png

 

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.
 

View solution in original post

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

 

Nandor_0-1624518914574.pngNandor_1-1624518943041.png

 

These are the two source tables: Prices_Store1 and Prices_Store1

Nandor_2-1624518989361.png

 

Please help me to modify the code for this case.

 

Thank you,

Best regards

 

Thank you, it's working!

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors