UNSOLVED - How to show changed data between multiple tables

[FU]

Hey all,

I have a sharepoint folder which contains weekly snapshots of my Excel data file. 

I would like to be able to compare each file against one another and show where there are changes. 

E.g. if a client wants to view what changes have been made to financial data from week 1 and week 3, they can select week 1 and week 3 filters and using conditional formatting (or some method) see if there are any changes in that column

Image 1: This is how I capture my weekly snapshots

Image 2: I then connect my Power BI dashboard to my SharePoint folder and read all the files as shown below.

Image 3: Now for the visual part, I want to be able to select 2 reports/dates (as shown in the filter) and immediately see where there are changes between the 2 reports. The current view shows the full table from week 1 and week 3 but I'm only interested in seeing where there has been changes.

Any suggestions would be hugely appreciated!

[v-rzhou-msft]

Hi @FU ,

According to your screenshot, I think you have combined the excel files from sharepoing folder in Power BI. Now you want to compare the values in same place in different name groups and flag the data which have changed. Here I suggest you to add an index by name group firstly.

The new table should look like as below.

Whole M Code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCsgvTy1ScPJUcEksSTTWq8gprlDSUVKK1cEl5RxsYGBgREiBMSEFJoQUmGIqMMLtPCNCzjMiZDuJCswwFeB2HSHHEQo6Qk5DD7hYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"Configuration State ID" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Configuration State ID", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {{"Count", each _, type table [Name=nullable text, Configuration State ID=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Index",1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Configuration State ID", "Index"}, {"Custom.Configuration State ID", "Custom.Index"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Custom",{{"Custom.Index", "Index"}})
in
    #"Renamed Columns"

For reference:Create Row Number for Each Group in Power BI using Power Query

Then I suggest you to create two unrelated Name tables for two silcers.

Measures:

Custom.Configuration State ID 1 = 
CALCULATE(MAX('Snapshot - Overview1'[Custom.Configuration State ID]),USERELATIONSHIP(DimName1[Name],'Snapshot - Overview1'[Name]))

Custom.Configuration State ID 2 = 
CALCULATE(MAX('Snapshot - Overview1'[Custom.Configuration State ID]),USERELATIONSHIP(DimName2[Name],'Snapshot - Overview1'[Name]))

Conditional formatting Color = 
IF([Custom.Configuration State ID 1] <> [Custom.Configuration State ID 2],"Red")

Here I use backgroup conditional formatting for Index. Result is as below.

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.

[FU]

@v-rzhou-msft I am trying to achieve something like the below. Also I have more than 1 table in my Excel sheet.

Each weekly snapshot is the exact same column types/names/quantity - the only different may be the data itself. How will the measures look for more than 1 table? I have sample sheets here https://we.tl/t-kN7fYQnfmn

[FU]

How would the conditional formatting measure look like for multiple columns?

[FU]

@v-rzhou-msft 
This is very helpful and I think very close to the solution.

Let's assume each week I have a new Excel file that is being snapshotted and stored into my SharePoint folder - would this also be ready?

Also it's worth noting - I have multiple tables in my Excel file. For example in the last image i've attached you can see 'Overview' and 'Milestones' in the top left and the idea with those is to have them as buttons where they are conditionally formatted to show if there are any changes in those tables. Does that make sense?

For example If i have multiple buttons down the left all representing a table in my Excel file, in a reset view I want to be able to:

Step 1: select two reports/dates e.g. Power BI Data1 vs Power BI Data2

Step 2: on the left hand side where my buttons will be - be able to see which tables have changes in them by filling the button in green for example.

Step 3: click on the highlighted button on the left and then be able to see where the changes are on the tables as per your example above. 

Does that make sense or have I confused you @v-rzhou-msft