Power Query - creating key column based on column names and row values

[spencerhoyle]

I am looking to creating a "key" column with the only certain column names and values for those not null.

An examples is....

|        A        |       B        |        C        | ->>> |    key    |

|        1        |     null       |        3        | ->>> "A=1,C=3"

|      null      |       2         |       null     | ->>> "B=2"

|        2        |       4         |        5        | ->>> "A=2,B=4,C=5"

Is there a way of extracting only column names where value is not null and creating a key column like the one above?

[v-luwang-msft · Accepted solution]

HI @spencerhoyle ,

In my opinion ,you need to create three column ,then combine:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQKhWJ1oEG2ExAYiYzDbECoB5MUCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [a = _t, b = _t, c = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"a", Int64.Type}, {"b", Int64.Type}, {"c", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom1", each if [a] is null and [b] is null  then "" 
else if  [a] <>null and [b] is null  then "a="&Number.ToText([a])   else   "a="&Number.ToText([a])&","),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom2", each if [b] is null and [c] is null  then "" 
else if  [b] <>null and [c] is null  then "b="&Number.ToText([b])   else   "b="&Number.ToText([b])&","),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom3", each if [c] is null then "" else   "c="&Number.ToText([c])),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Final", each [Custom1]&[Custom2]&[Custom3]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"Custom1", "Custom2", "Custom3"})
in
    #"Removed Columns"

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,
Lucien