Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Column from Different Tables

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!

Need Help
Show and Tell
Tutorial Requests

5 Replies

  • Floriankx's avatar
    Floriankx
    Solution Sage
    8 years ago

    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's avatar
      Anonymous
      Not applicable
      8 years ago

      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's avatar
        Floriankx
        Solution Sage
        8 years ago

        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's avatar
    Hactogeek
    Frequent Visitor
    8 years ago

    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's avatar
      Anonymous
      Not applicable
      8 years ago

      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"