Forum Discussion

FlorisMK's avatar
FlorisMK
Icon for Helper I rankHelper I
3 years ago
Solved

Replace specific value with column name in variable number of columns

Starting from a calendar ListObject in Excel which has rows for each date (first column = "Date"), columns for each employee, and a "V" in each cell where an employee is on vacation on that date. I ...
Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    3 years ago

    Yes, 2nd statement is the answer where Source will be replaced with #"Changed Type". So the code becomes below

    let
    Source = Excel.CurrentWorkbook(){[Name="TablePlannedVacations"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Aparupa", type text}, {"Eunice", type text}, {"Floris", type text}, {"Frank", type text}, {"Gijsbert", type text}, {"Jasper", type text}, {"Ken", type text}, {"Mahesh", type text}, {"Mark", type text}, {"Niels", type text}, {"Remco", type text}, {"Rob", type text}, {"Sepideh", type text}, {"Stan", type text}, {"Thijs", type text}}),
Custom1 = Table.FromRecords(
    Table.TransformRows(#"Changed Type", (r) => List.Accumulate(Table.ColumnNames(#"Changed Type"), r, (s,c)=> 
        Record.TransformFields(s,{{c, each if _ = "V" then c else null}})))
        , Value.Type(#"Changed Type"))
in
    Custom1
  • slorin's avatar
    slorin
    3 years ago

    Hi,

     

    Unpivot, Replace Value, Pivot

     

    let
    Source = Your_Source,
    Unpivot = Table.UnpivotOtherColumns(Source, {"Date"}, "Attribute", "Value"),
    Replace_Value = Table.ReplaceValue(Unpivot,"V",each [Attribute],Replacer.ReplaceText,{"Value"}),
    Pivot = Table.Pivot(Replace_Value, List.Distinct(Replace_Value[Attribute]), "Attribute", "Value")
    in
    Pivot

    Stéphane 

slorin's avatar
slorin
Icon for Super User rankSuper User
3 years ago

Hi,

 

Unpivot, Replace Value, Pivot

 

let
Source = Your_Source,
Unpivot = Table.UnpivotOtherColumns(Source, {"Date"}, "Attribute", "Value"),
Replace_Value = Table.ReplaceValue(Unpivot,"V",each [Attribute],Replacer.ReplaceText,{"Value"}),
Pivot = Table.Pivot(Replace_Value, List.Distinct(Replace_Value[Attribute]), "Attribute", "Value")
in
Pivot

Stéphane 

  • FlorisMK's avatar
    FlorisMK
    Icon for Helper I rankHelper I
    3 years ago

    Hi slorin , thanks for your quick response. I'm afraid though that as a relative noob, I'm not quite clear how this works. Could you provide some explanation with the code?

  • FlorisMK's avatar
    FlorisMK
    Icon for Helper I rankHelper I
    3 years ago

    I've accepted this as my final solution, because it's lean, clear, and gives me a convenient opportunity between Unpivot and Pivot to do some additional data replacements while I have the unpivoted single data column.