How to automate "replace values" in multiple columns by their column name using M?

[Merleau]

Hello,

I think thisi s Power Query question.

I am new to Power Query and M language. Still looking for good tutorial on M.

This is my problem.

I have a table with more than 40 columns.

Originally those columns contain "0" and "1"

I need them to contain only their respective column names instead of "0" or "1"s.

I did the following:

1- Change all column type from numeric to text

2- Bulk replaces values "0" with nothing, empty.

3- The last step is to replace each "1' in the column by the column name and I don't know how to automate it. I can do it manually by using "replace value" in about 30 columns but that is tedious.

Can somebody pls help?

Thank you.

1.  

Id	Green	red	yellow
	0	1	0
	1	1	0
	0	0	1

To become

Id	Green	red	yellow
		red
	Green	red
			yellow

[Greg_Deckler]

@ImkeF 

[ImkeF · Accepted solution]

Hi @Merleau  

it would be easiest to unpivot on the ID-column and then add a column with the replacement-command. Then pivot back.

Add the following code into the advanced editor of a new query and you can follow the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIAYggdqxOtZATlIUSMoWog6mJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Id = _t, Green = _t, red = _t, yellow = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Id"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each if [Value] = "1" then [Attribute] else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Value"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute]), "Attribute", "Custom")
in
    #"Pivoted Column"

[Merleau]

Thanks so much ImkeF.

This approach completely works.