Conditional merge in query editor

[ale-hd]

Hi all, I am stucked in something... extremely difficult to me.

Here you can download the (very small) pbix file 

Have 2 tables, "table 2" has unique values in column "ID 2".  

TABLE 1

TABLE 2

When "ID 2" is matching I need to obtain in Table 1 all of the corresponding columns from Table 2.

This is basic and easy. BUT in table 1 there are columns where "ID 2" is empty, therefore, as exptected, I obtain "null" values when this happens.

MERGED TABLE: TABLE 1 GETS VALUES FROM TABLE 2 WHEN "ID 2" IS MATCHING

What I need is:

when "ID 2" in Table 1 is empty I need the cells with "null" values, being populated with the values from the first row (from up to down) with matching "ID 1" in table 2.

So, if I could write an "If" statement I would say:

If "ID 2" in "Table 1" is not empty than merge all the columns from the "ID 2" matching rows in "Table 2". Otherwise merge all the columns from the first row met, reading from up to down, matching the "ID 1".

In the exampe I should obtain "Good - New york - Usa" in lines 3 and 4; "Any , Sadsfas , fdd... " in line 8

Please note: for some reason I necesseraly have to manage this in Query Editor.

Thank you so much for your help, Alessandro

[lbendlin · Accepted solution]

You can iterate through the table (you have to do it for each column) and replace null with the column value in the first row

[column] {0}

Here is the first step to do that - you still need to add the section logic.

let
    Source = Table.NestedJoin(#"Table 1", {"ID 2"}, #"Table 2", {"ID 2 "}, "Table 2", JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(Source, "Table 2", {"DETAIL 1", "DETAIL 2 ", "DETAIL 3"}),
    #"Replaced Value" = Table.ReplaceValue(Expand,"",Expand[ID 2]{0},Replacer.ReplaceValue,{"ID 2"})
in
    #"Replaced Value"