Update only changed records in dats source

[Anonymous]

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.

[Anonymous · Accepted 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.