topic How to automate "replace values" in multiple columns by their column name using M? in DAX Commands and Tips

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

Merleau — Wed, 19 Feb 2020 14:35:50 GMT

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.

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

To become

Id	Green	red	yellow
		red
	Green	red
			yellow

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

Greg_Deckler — Wed, 19 Feb 2020 15:27:43 GMT

ImkeF

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

ImkeF — Wed, 19 Feb 2020 20:04:41 GMT

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"

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

Merleau — Mon, 23 Mar 2020 17:36:30 GMT

Thanks so much ImkeF.

This approach completely works.