Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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.

 

 

 AB
CDEFG
1AccountMappingAbsolute MappingWildcard x1Wildcard x2Wildcard x3Wildcard x4

2

12345

1234512345  12???1????
3546125461? 5461?   
498745987??  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

1 ACCEPTED SOLUTION
CNENFRNL
Community Champion
Community Champion

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

Screenshot 2021-10-08 125208.png


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!

View solution in original post

5 REPLIES 5
CNENFRNL
Community Champion
Community Champion

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

Screenshot 2021-10-08 125208.png


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!

jennratten
Super User
Super User

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)

 

 

jennratten_0-1633607093823.png

 

Kudos given for using the PQ coalesce operator.

Very underutilised technique.

Nice.



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Thanks!!

Anonymous
Not applicable

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?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors