Compare tables

Hi Anonymous,

 

We can create some calculated tables to work on it.

 

T1 = 
SELECTCOLUMNS (
    Table2,
    "client", Table2[Client],
    "jan", CALCULATE ( SUM ( Table2[Jan] ) )
        - CALCULATE (
            SUM ( 'Table1'[Jan] ),
            FILTER ( Table1, Table1[Client] = Table2[Client] )
        )
)

T2 = 
SELECTCOLUMNS (
    Table2,
    "client2", Table2[Client],
    "feb", CALCULATE ( SUM ( Table2[Feb] ) )
        - CALCULATE (
            SUM ( 'Table1'[Feb] ),
            FILTER ( Table1, Table1[Client] = Table2[Client] )
        )
)

T3 = 
SELECTCOLUMNS (
    Table2,
    "client3", Table2[Client],
    "mar", CALCULATE ( SUM ( Table2[Mar] ) )
        - CALCULATE (
            SUM ( 'Table1'[Mar] ),
            FILTER ( Table1, Table1[Client] = Table2[Client] )
        )
)

Then create relationship between the three tables by client column.

 

Then in T1, we can create two calculated columns to get the result as we need.

 

_feb = RELATED(T2[feb])

_Mar = RELATED(T3[mar])

 

For more details, please check the pbix as attached.

 

Regards,

Frank

 

 

Hi Anonymous ,

 

To do that in Power query, here is the M code for your reference.

 

let
    Source = Table.NestedJoin(Table2,{"Client"},Table1,{"Client"},"Table1",JoinKind.LeftOuter),
    #"Expanded Table1" = Table.ExpandTableColumn(Source, "Table1", {"Client", "Jan", "Feb", "Mar"}, {"Table1.Client", "Table1.Jan", "Table1.Feb", "Table1.Mar"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Table1",null,0,Replacer.ReplaceValue,{"Table1.Jan", "Table1.Feb", "Table1.Mar"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "J", each [Jan]-[Table1.Jan]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "F", each [Feb]-[Table1.Feb]),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "M", each [Mar]-[Table1.Mar]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Mar", "Table1.Client", "Table1.Jan", "Table1.Feb", "Table1.Mar", "Jan", "Feb"})
in
    #"Removed Columns"

 

 

Regards,

Frank

Hi Anonymous ,

 

Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.

 

Regards,
Frank