Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 ...
Need Help
  • MBreden's avatar
    MBreden
    4 years ago

    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

     

  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    OK. Then instead of

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

    use

    (col) => {col, each if _ = "Yes" then col else _, type text}
MBreden's avatar
MBreden
Helper I
4 years ago

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's avatar
    Anonymous
    Not applicable
    4 years ago

    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