Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
80 | |
67 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |