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.
There are few columns in data. I want to get the latest non-null value from these columns.
The NewOutputCol is my output column.
In first row, L1 column has the latest non-null value and therefore, output is "A".
In second row, L2 column has the latest non-null value and therefore, output is "E".
In thirs row, L3 column has the latest non-null value and therefore, output is "Z".
In real data, I have 10 such columns to scan from and over 90,000 rows.
Sample data:
L1 | L2 | L3 | NewOuputCol |
A | null | null | A |
D | E | null | E |
X | null | Z | Z |
Solved! Go to Solution.
Hi @PC20
In Power Query, you can create this custom column
List.Last(List.Select(Record.FieldValues(_), each _ <> null))
Proud to be a Super User!
Hi @PC20
In Power Query, you can create this custom column
List.Last(List.Select(Record.FieldValues(_), each _ <> null))
Proud to be a Super User!
@PC20 Probably best if you unpivot your columns and add and index perhaps. Otherwise, maybe this:
Column =
VAR __Data = [L1] & "|" & [L2] & "|" & [L3]
VAR __Table =
ADDCOLUMNS(
GENERATESERIES(1,3,1),
"__Data", PATHITEM(__Data,[Value],TEXT)
)
VAR __Max = MAXX(FILTER(__Table,[__Data] <> BLANK()),[Value])
VAR __Result = MAXX(FILTER(__Table,[Value] = __Max),[__Data])
RETURN
__Result
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 |
---|---|
104 | |
96 | |
80 | |
67 | |
62 |
User | Count |
---|---|
137 | |
106 | |
104 | |
81 | |
63 |