populate a row with the name of a field

[jerryr125]

Hi - I have the following data and I would like to populate any non-null values with the field name concatenated before the value.

Example

Starting Data

ID	Name1	Name2	Name3
1	Required	Approved	null
2	Expired	Required	Overdue
3	null	Required	null

Final Table - What I am looking to do :

ID	Name1	Name2	Name3
1	Name1-Required	Name2-Approved	null
2	Name1-Expired	Name2-Required	Name3-Overdue
3	null	Name2-Required	null

Any thoughts  ? Jerry

[lbendlin · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpKLSzNLEpNATIdCwqK8svAzLzSnBylWJ1oJSMgx7WiAKoCSbF/WWpRSmkqWJExTAeKCogZsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Name1 = _t, Name2 = _t, Name3 = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"ID"}, "Attribute", "Value"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns",each [Value],each if [Value]="null" then null else [Attribute] & "-" & [Value],Replacer.ReplaceValue,{"Value"}),
    #"Pivoted Column" = Table.Pivot(#"Replaced Value", List.Distinct(#"Replaced Value"[Attribute]), "Attribute", "Value")
in
    #"Pivoted Column"

c

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

[dufoq3 · Accepted solution]

Hi @jerryr125, different approach with List.Accumulate:

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpKLSzNLEpNATIdCwqK8svAzLzSnBylWJ1oJSMgx7WiAKoCSbF/WWpRSmkqWJExTAeKCogZsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Name1 = _t, Name2 = _t, Name3 = _t]),
    Transformed = List.Accumulate(
        List.Skip(Table.ColumnNames(Source)),
        Source,
        (s,c)=> Table.TransformColumns(s, {{c, each if List.Contains({null, "null"}, _) then null else c & "-" & _, type text}}) )
in
    Transformed

[Omid_Motamedise · Accepted solution]

RIGHT CLICK ON ID COLUMN AND PICK UNPIVOT OTHER COLUMN TO REACH THE NEXT TABLE

Add a new custom column by the next formula

[Attribute]&"-"&[Value]

to reach the next result

remove column valu and then select attribute column and from transform tab pick pivot and then pick custom column and like the next image from advance setting pick do not aggregate

pres ok to result in 

[dufoq3 · Accepted solution]

Hi @jerryr125, different approach with List.Accumulate:

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpKLSzNLEpNATIdCwqK8svAzLzSnBylWJ1oJSMgx7WiAKoCSbF/WWpRSmkqWJExTAeKCogZsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Name1 = _t, Name2 = _t, Name3 = _t]),
    Transformed = List.Accumulate(
        List.Skip(Table.ColumnNames(Source)),
        Source,
        (s,c)=> Table.TransformColumns(s, {{c, each if List.Contains({null, "null"}, _) then null else c & "-" & _, type text}}) )
in
    Transformed

[lbendlin · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpKLSzNLEpNATIdCwqK8svAzLzSnBylWJ1oJSMgx7WiAKoCSbF/WWpRSmkqWJExTAeKCogZsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Name1 = _t, Name2 = _t, Name3 = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"ID"}, "Attribute", "Value"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns",each [Value],each if [Value]="null" then null else [Attribute] & "-" & [Value],Replacer.ReplaceValue,{"Value"}),
    #"Pivoted Column" = Table.Pivot(#"Replaced Value", List.Distinct(#"Replaced Value"[Attribute]), "Attribute", "Value")
in
    #"Pivoted Column"

c

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.