Forum Discussion

FabryZ's avatar
FabryZ
Frequent Visitor
4 years ago
Solved

How to relate two tables without a common key (easy in SQL, but in Power Query?)

Hi all, I'm new to Power query (so, I apologize for stupid questions...), but I'm trying to compare sql query and power query, and I'm not able to merge these two tables. table1: id_emp, name, sa...
Need Help
  • KT_Bsmart2gethe's avatar
    KT_Bsmart2gethe
    4 years ago

    Hi FabryZ ,

     

    I downloaded the example file.

     

    I updated the code provided by Anonymous and removed "each" from the syntax.

     

    Copy the code below to a blank query to the example file and you know how it works.

     

    Code:

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id_emp", Int64.Type}, {"name", type text}, {"salary", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Add Table 2", (x)=> Table.SelectRows( Table2, each x[salary]>[min_sal] and x[salary]<[max_sal])),
    #"Expanded Add Table 2" = Table.ExpandTableColumn(#"Added Custom", "Add Table 2", {"id_cat", "min_sal", "max_sal"}, {"id_cat", "min_sal", "max_sal"})
    in
    #"Expanded Add Table 2"

     

     Regards

    KT

FabryZ's avatar
FabryZ
Frequent Visitor
4 years ago

Hi Vera, thank you.
Unfortunately I am unable to proceed further, because after creating the field, I am unable to recall table2 from the rows of table1. I have to study the use of function syntax. I'm really sorry, but thanks anyway!

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi FabryZ 

 

my bad, when you paste it Power Query Editor will auto generate "each" then it becomes a Function...attached my file with dummy data for your reference

  • FabryZ's avatar
    FabryZ
    Frequent Visitor
    4 years ago

    Hi Vera and thanks a lot for your patience! 🙂
    Your attachment refers to power bi desktop software, my tables are in Excel.
    So, I followed your previous instructions to successfully create the field, as in the linked example, but I was unable to connect function with table2. However, I resolved with:
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Add Table 2", each Table2), as KT suggests, even if I am interested in your solution with function!
    Best!

    Example.xlsx 

    • KT_Bsmart2gethe's avatar
      KT_Bsmart2gethe
      Icon for Impactful Individual rankImpactful Individual
      4 years ago

      Hi FabryZ ,

       

      I downloaded the example file.

       

      I updated the code provided by Anonymous and removed "each" from the syntax.

       

      Copy the code below to a blank query to the example file and you know how it works.

       

      Code:

      let
      Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
      #"Changed Type" = Table.TransformColumnTypes(Source,{{"id_emp", Int64.Type}, {"name", type text}, {"salary", Int64.Type}}),
      #"Added Custom" = Table.AddColumn(#"Changed Type", "Add Table 2", (x)=> Table.SelectRows( Table2, each x[salary]>[min_sal] and x[salary]<[max_sal])),
      #"Expanded Add Table 2" = Table.ExpandTableColumn(#"Added Custom", "Add Table 2", {"id_cat", "min_sal", "max_sal"}, {"id_cat", "min_sal", "max_sal"})
      in
      #"Expanded Add Table 2"

       

       Regards

      KT

      • FabryZ's avatar
        FabryZ
        Frequent Visitor
        4 years ago

        Hi KT_Bsmart2gethe  and Anonymous ,
        You're right, and now I understand clarification of Vera, too! You have to be patient, I am really at the beginning!
        Thank you very very much all! Have a great next Weekend!