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

CNENFRNL's avatar
CNENFRNL
Icon for Community Champion rankCommunity Champion
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