Change value by header name

[Anonymous]

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 Yes with the name of column. I know how to do it one by one, and copying and pasting the header name, but not for all at the same time and naming with code the header name directly in M Query. 

= Table.ReplaceValue(#"Replaced Value1","Yes","Header Name 1",Replacer.ReplaceText,{"Header Name 1"})

Thanks

[MBreden · Accepted solution]

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

[MBreden · Accepted solution]

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

[Anonymous]

This is also working, but I have taken the solution from Alexis cause the necessary steps are less. 

Thanks for your reply in any case

[AlexisOlson]

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"

[Anonymous]

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? 

[AlexisOlson]

Can you give a simple example demonstrating what you mean?

[Anonymous]

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

[AlexisOlson · Accepted solution]

OK. Then instead of

(col) => {col, each _ & " " & col, type text}

use

(col) => {col, each if _ = "Yes" then col else _, type text}

[Anonymous]

Thanks, this is correctly working

[jennratten]

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.

[Anonymous]

Thanks for your answer. I was searching something to be done without pivot and unpivot in order to do it with the replace function, although this is also having the same result. I will leave the issue open in case someone knows how to do it and if not I will accept this solution.