Megre Query with Condition

[IgorDo]

Hi all,

I have a table A with say two columns: "Unique ID" and "Type". And I have a table B with another three columns: "Unique ID", "Type", and "Value".

I would like to merge them in such fashion, that when "Type"=1, then the corresponding value from table B is taken, and only when there is no entry with "Type"=1, I would take other valuee from table B. (dont bother about multiple entries and so on... I solved it).

In SQL I would solve it with two consequtive joins and a where condition. How would I do it in PowerBI Query Editor?

Many thanks in advance,

Igor.

[MarcelBeug]

You can do something similar like in the query below.

This will merge on ID and Type and merge again only on ID.

Then a column is added that picks one of the 2 nested Tables B.

It may not be the correct interpretation (as I'm confused as where the type 1 should be found: in table A or table B), but this would be the direction:

let
    Source = Table.NestedJoin(TableA,{"Unique ID", "Type"},TableB,{"Unique ID", "Type"},"TableB_Types1",JoinKind.LeftOuter),
    #"Merged Queries" = Table.NestedJoin(Source,{"Unique ID"},TableB,{"Unique ID"},"TableB_Other",JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(#"Merged Queries", "TableB", each if [Type] = 1 then [TableB_Types1] else [TableB_Other]),
    #"Expanded TableB" = Table.ExpandTableColumn(#"Added Custom", "TableB", {"Type", "Value"}, {"TableB.Type", "TableB.Value"})
in
    #"Expanded TableB"