Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
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.
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]
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.
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"
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"