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}
You can transform multiple columns by using List.Transform to build a list of column transformations like this:
= Table.TransformColumns(
#"Changed Type",
List.Transform(
{"Boolean", "Letter"},
(col) => {col, each _ & " " & col, type text}
)
)
Starting table:
Transformed table:
Full sample query you can paste into the Advanced Editor of a new blank query:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WikwtVtJRcgRiQ6VYHRjfCYiNwHy/fCDTGYiNlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Boolean = _t, Letter = _t, Number = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Boolean", type text}, {"Letter", type text}, {"Number", Int64.Type}}),
#"Embedded Headers" = Table.TransformColumns(#"Changed Type", List.Transform({"Boolean", "Letter"}, (col) => {col, each _ & " " & col, type text}))
in
#"Embedded Headers"Thanks for your answer. My idea was changing the Yes by the Header Name, not include in all the columns the header name. Is it a way to perform this?
Can you give a simple example demonstrating what you mean?
Yes sure:
Initial data
Title Name 1 Name 2 Name 3 Name 4 A Yes No No Yes B No Yes No Yes Expected outcome
Title Name 1 Name 2 Name 3 Name 4 A Name 1 No No Name 4 B No Name 2 No Name 4