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}
jennratten's avatar
jennratten
Super User
4 years ago

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

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.