Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi All,
I am kind of new to power bi.
i have this requirement where i need to update the data every week. Source files is excel and it will get replaced every week in destination folder and i want to show only data where there was changes to the particular row since last week in a table.
Exxample below:
Source file Week 1
ID Sales
101 100
102 150
103 140
104 200
Source file Week 2
ID Sales
101 100
102 180
103 100
104 200
now have this is mind that soruce file week 1 will be replaced by source file week 2 in destination folder.
and i want to show in table only the rows where sales amount is changed be it increase or decrease (Eg ID 102 and 103).
How can i achieve this?
Any help would be appreciated.
Thanks in advanced.
Solved! Go to Solution.
Hi @Anonymous
We can use Power Query to achieve your goal. But we need to compare the new table with the old table, so it is better to store two files (last week and current week) in your folder instead of replacing old one directly.
Now my new data is Week2, so I keep Week1 and Week2 in my folder.
Import data into PBI Desktop and add customer column WeekN Key in two tables.
WeekN Key
=
Text.Combine(
List.Transform(
Record.FieldValues(_),
each Replacer.ReplaceValue(
Text.From(_),
null,
"")),
";")
Week1:
Week2:
Build a Blank Query in Query Field and build a new table(Change) by this code.
let
Source = Table.NestedJoin(Week2, {"Week2 Key"}, Week1, {"Week1 Key"}, "HistoryData", JoinKind.LeftAnti),
#"Removed Columns" = Table.RemoveColumns(Source,{"Week2 Key", "HistoryData"})
in
#"Removed Columns"
Result:
If you want to compare Week3 and Week2 table, you can upload Week3, delete Week1. And build WeekN Key column in Week3. Then change the parameter in Change table code. Replace Week2 by Week3 and replace Week1 by Week2. Then you can get the result.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous
We can use Power Query to achieve your goal. But we need to compare the new table with the old table, so it is better to store two files (last week and current week) in your folder instead of replacing old one directly.
Now my new data is Week2, so I keep Week1 and Week2 in my folder.
Import data into PBI Desktop and add customer column WeekN Key in two tables.
WeekN Key
=
Text.Combine(
List.Transform(
Record.FieldValues(_),
each Replacer.ReplaceValue(
Text.From(_),
null,
"")),
";")
Week1:
Week2:
Build a Blank Query in Query Field and build a new table(Change) by this code.
let
Source = Table.NestedJoin(Week2, {"Week2 Key"}, Week1, {"Week1 Key"}, "HistoryData", JoinKind.LeftAnti),
#"Removed Columns" = Table.RemoveColumns(Source,{"Week2 Key", "HistoryData"})
in
#"Removed Columns"
Result:
If you want to compare Week3 and Week2 table, you can upload Week3, delete Week1. And build WeekN Key column in Week3. Then change the parameter in Change table code. Replace Week2 by Week3 and replace Week1 by Week2. Then you can get the result.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.