Forum Discussion
Change value by header name
Hello, Is there any way to replace the value of a cell with the Header Name for different columns at the same time? I have multiple columns when the value is Yes, and I need to change this ...
how about this solution?
let Quelle = Excel.CurrentWorkbook(){[Name="PFC"]}[Content], #"Tiefer gestufte Header" = Table.DemoteHeaders(Quelle), #"Transponierte Tabelle" = Table.Transpose(#"Tiefer gestufte Header"), ListHeader = List.RemoveFirstN(Table.ColumnNames(#"Transponierte Tabelle"),1), ErsetzterWert = Table.ReplaceValue(#"Transponierte Tabelle","Yes", each _[Column1],Replacer.ReplaceText,ListHeader), #"Transponierte Tabelle1" = Table.Transpose(ErsetzterWert), #"Höher gestufte Header" = Table.PromoteHeaders(#"Transponierte Tabelle1", [PromoteAllScalars=true]) in #"Höher gestufte Header"Melanie
OK. Then instead of
(col) => {col, each _ & " " & col, type text}use
(col) => {col, each if _ = "Yes" then col else _, type text}
Hello - you can unpivot the columns in which the values needing to be replaced, which will give you one column for all of the column names and one column for all of their corresponding values. Then apply the replacements function to the values column and pivot back.