Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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. ...
Need Help
  • CNENFRNL's avatar
    CNENFRNL
    4 years ago
    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

jennratten's avatar
jennratten
Icon for Super User rankSuper User
4 years ago

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)

 

 

 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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?