Column from Different Tables

[Anonymous]

Hey guys, very noob question.

I have two tables with two costs columns. Like this:

[Table1]

ID - Cost

01 - 100

02 - 200

03 - 300

[Table2]

ID - Cost

01 - 110

02 - 220

03 - 300

What I need to do is to compare each one of these values, see which ones are different (based on ID) and list them on a new column.

I tried to merge the tables on query editor but somehow I get 0 matched rows. So I was thinking in using dax.

Can someone help me with this?

Thanks!

[Floriankx]

Hello,

usually they should be mergable in Power Query. Are both ID columns formatted the same without any spaces or similar?

Nevertheless you can relate the two ID columns.

If both columns contain unique values you can just add a calculated column =[Cost]-Related(ID_Cost2[Cost]).

It is depending of the direction of your relation in which table you are able to add the RELATED.

[Anonymous]

Hi @Floriankx

I tried creating the calculated column =[Cost]-Related(ID_Cost2[Cost]), but it did not work.

I have both tables connected through a fact table, like this:

[Table1] N -------1[Fact]1-------N[Table2]

[Floriankx]

Hello,

I assume you add your calculated table in the Fact Table.

The relations have to be the other way arround. So you need to drag the Fact ID on the data ID.

I don't how excatly this works with the direction of the relations but I know it works this way.

Regarding your Problem in Power Query it would be helpful to know which error is shown.

[Hactogeek]

Hey,

If I understand well your question, the next code do what you want.

let
    Source = Table1,,
    #"Type modifié" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Cost", Int64.Type}}),
    #"Requêtes fusionnées" = Table.NestedJoin(#"Type modifié",{"ID"},Table2,{"ID"},"Table2",JoinKind.LeftOuter),
    #"Table2 développé" = Table.ExpandTableColumn(#"Requêtes fusionnées", "Table2", {"ID", "Cost"}, {"Table2.ID", "Table2.Cost"}),
    #"Personnalisée ajoutée" = Table.AddColumn(#"Table2 développé", "Personnalisé", each [Cost]-[Table2.Cost]),
    #"Lignes filtrées" = Table.SelectRows(#"Personnalisée ajoutée", each [Personnalisé] <> 0)
in
    #"Lignes filtrées"

[Anonymous]

Hey @Hactogeek, thanks for the reply.

I'm not sure what I'm doing wrong here, can you help me once more?

let
    Source = Cost,
    #"Type modifié" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Total -2018", Int64.Type}}),
    #"Requêtes fusionnées" = Table.NestedJoin(#"Type modifié",{"ID"},Table2,{"ID"},"Cost",JoinKind.LeftOuter),
    #"Table2 développé" = Table.ExpandTableColumn(#"Requêtes fusionnées", "Cost", {"ID", "Total -2018"}, {"Table2.ID", "Table2.Cost"}),
    #"Personnalisée ajoutée" = Table.AddColumn(#"Table2 développé", "Personnalisé", each [Cost]-[Table2.Cost]),
    #"Lignes filtrées" = Table.SelectRows(#"Personnalisée ajoutée", each [Personnalisé] <> 0)
in
    #"Lignes filtrées"