cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sakkie6
New Member

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

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

View solution in original post

jennratten
Solution Supplier
Solution Supplier

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.

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?

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors