Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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.
Proud to be a Datanaut!
Thanks!!
Thank you for the solution in PowerBI.
Turns out I need the PowerPivot formula and not PowerBI, unless there is a way to merge the two?