Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Solved! Go to 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"
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"
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
92 | |
86 | |
85 | |
66 | |
49 |
User | Count |
---|---|
140 | |
113 | |
104 | |
64 | |
60 |