Forum Discussion

bernate's avatar
bernate
Helper III
2 years ago
Solved

Get 2nd Result from Merge Queries

Hello, I am using Merge Queries in Power BI as a VLOOKUP to get the resulting row value based on the columns I am matching on. Table 1 contains the names of people, Table 2 contains the names and the departments the people work in. I want to get the result shown in Merged Table, where I list the names of the people and 2 columns for people who may work in more than 1 department. I can get the Department 1 column in Power Query by doing a merge of Table 1 and Table 2, but how can I get the Department 2 column?

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    I would do it like this:

     

    let
    Source = Table.NestedJoin(Table1, {"Name"}, Table2, {"Name"}, "Table2", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(Source, "Department 1", each [Table2]{0}[Department]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Department 2", each try [Table2]{1}[Department] otherwise null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Table2"})
    in
    #"Removed Columns"

     

     

     

    --Nate

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    I would do it like this:

     

    let
    Source = Table.NestedJoin(Table1, {"Name"}, Table2, {"Name"}, "Table2", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(Source, "Department 1", each [Table2]{0}[Department]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Department 2", each try [Table2]{1}[Department] otherwise null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Table2"})
    in
    #"Removed Columns"

     

     

     

    --Nate

  • Ahmedx's avatar
    Ahmedx
    Super User
    2 years ago

    pls try this

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJUitWJVrowCcg0gjAXApnGCCZQNBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Department = _t]),
    group = Table.Group(Source, "Name", {"tmp", 
(x)=>   [  a = x[Department],
          b = Table.FromRows( {a}, List.Transform({1..List.Count(a)},(x)=>"Department"& Text.From(x)))
   ][b]
}),
    #"Expanded tmp" = Table.ExpandTableColumn(group, "tmp", {"Department1", "Department2"})
in
    #"Expanded tmp"