Forum Discussion

dd88's avatar
dd88
Post Patron
2 years ago

Move columns into one column

in Table view, I have a table with columns of different tasks types, tasks due date and name assigned to task. the data source is an excel spreadsheet. eg of layout.    Name     Task 1     Tas...
Need Help
Ashish_Mathur's avatar
Ashish_Mathur
Super User
2 years ago

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Name"}, "Task type", "Date"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each [#"Task type"]=[Date]),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = false)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Columns","dueDate","",Replacer.ReplaceText,{"Task type"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","DueDate","",Replacer.ReplaceText,{"Task type"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value1",{{"Date", type date}})
in
    #"Changed Type"

Hope this helps.