Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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.
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
Thank you, it's working!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
101 | |
45 | |
19 | |
13 | |
11 |