Compare tables

[Anonymous]

Hi,

I've Table 1 and table2, i wish to add a new table as mentioned below with net difference in a new Page in Power BI.

Regards

Varun

[v-frfei-msft · Accepted solution]

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

[v-frfei-msft · Accepted solution]

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

[v-frfei-msft]

Hi @Anonymous,

We can create a calculated table as below.

Table = 
SELECTCOLUMNS (
    Table2,
    "Date", 'Table2'[Date],
    "Client", Table2[Client],
    "_Amount", Table2[Amount]
        - CALCULATE (
            SUM ( Table1[Amount] ),
            FILTER ( Table1, Table1[Client] = Table2[Client] )
        )
)

For more details, please check the pbix as attached.

Regards,

Frank

[Anonymous]

Hello Frank,

Thanls for your time. This Formula works for me.

Just  wish to eloborate on my requirement like above. Request you to please check above. 

I have a Table1 & Table2 and i created a new conditional Table (Targe Table) by using Merge Query, but i'm getting Client A as two records. My report should be as same in the above field. 

Please help.

Regards,

Frank

[v-frfei-msft · Accepted solution]

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

[Anonymous]

Thanks you Fran for your time. I hope this should work, let me try with my data set and come back.

I tried using the Merge Query option, i got all the result close as expeted but the calculation was wrong. It would be great if you could help me with the process.

Please let me kown if the process is right?

Regards,

Varun

[v-frfei-msft · Accepted solution]

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

[v-frfei-msft · Accepted solution]

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

[Anonymous]

Hi,

I need only the difference amount over month on month, between the two tables.