Forum Discussion
Find first non-blank value across columns
Hi
To say I am new to PowerBI is an understatement. Very powerful and cool tool though.
I have to map a value from a mapping table, based on priority.
A simplified view is shown below. In my real scenario I have multiple variables that are merged (xxxxx.yyyy.zzz), each with its own wildcard mapping.
There are multiple possible mapping combinations, and I have to find the first value, which will then be looked up from a different table.
| A | B | C | D | E | F | G | |
| 1 | Account | Mapping | Absolute Mapping | Wildcard x1 | Wildcard x2 | Wildcard x3 | Wildcard x4 |
2 | 12345 | 12345 | 12345 | 12??? | 1???? | ||
| 3 | 54612 | 5461? | 5461? | ||||
| 4 | 98745 | 987?? | 987?? | 9???? |
The formula I would use in B2 in Excel is to determine the appropriate mapping is :
"Index(C2:G2,MATCH(TRUE,LEN(C2:G2)>0,0)". It returns the first value it finds in the range.
How do I replicate this formula in PowerBI in Excel?
Thank you for the help.
Sakkie6
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjFV0oHTEGRoZG9vD6LtQXSsTrSSqYmZoRFEEsS0hysFy1pamCPpBvLsoQos4QY4OiYi9EB1xgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Account = _t, #"Absolute Mapping" = _t, #"Wildcard x1" = _t, #"Wildcard x2" = _t, #"Wildcard x3" = _t, #"Wildcard x4" = _t]), Mapping = Table.AddColumn(Source, "Mapping", each try List.Select(List.Skip(Record.ToList(_)), each Text.Length(Text.Trim(_)) > 0){0} otherwise "") in Mapping
5 Replies
Hello - this will return the first non-null value - just make sure the empty cells are null and not blank. If needed, you can replace blanks with nulls. I have also included an example of that.
Replace blanks with nulls:
Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Absolute Mapping", "Wildcard x1", "Wildcard x2", "Wildcard x3", "Wildcard x4"})Return the first non-null value:
Table.AddColumn(#"Previous Step", "Mapping", each [Absolute Mapping] ?? [Wildcard x1] ?? [Wildcard x2] ?? [Wildcard x3] ?? [Wildcard x4], type text)Kudos given for using the PQ coalesce operator.
Very underutilised technique.
Nice.
Thanks!!
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjFV0oHTEGRoZG9vD6LtQXSsTrSSqYmZoRFEEsS0hysFy1pamCPpBvLsoQos4QY4OiYi9EB1xgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Account = _t, #"Absolute Mapping" = _t, #"Wildcard x1" = _t, #"Wildcard x2" = _t, #"Wildcard x3" = _t, #"Wildcard x4" = _t]), Mapping = Table.AddColumn(Source, "Mapping", each try List.Select(List.Skip(Record.ToList(_)), each Text.Length(Text.Trim(_)) > 0){0} otherwise "") in Mapping
