Get 2nd Result from Merge Queries

[bernate]

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?

[Anonymous · Accepted solution]

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

[bernate]

Perfect, thank you Nate!

[Ahmedx]

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"