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
manvishah17's avatar
manvishah17
Solution Supplier
2 years ago

HI dd88 ,
I created sample Pbix file. 

 

  The steps are :

 

let
    Source = Excel.Workbook(File.Contents("C:\Users\**bleep**al Clinic(Rutvi)\Downloads\Book1.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}, {"Column2", type text}, {"Column3", type any}, {"Column4", type text}, {"Column5", type any}, {"Column6", type text}, {"Column7", type any}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", type text}, {"Column2", type text}, {"Column3", type any}, {"Column4", type text}, {"Column5", type any}, {"Column6", type text}, {"Column7", type any}}),
    #"Promoted Headers1" = Table.PromoteHeaders(#"Changed Type1", [PromoteAllScalars=true]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers1",{{"Name    ", type text}, {"Task 1    ", type text}, {"Task 1 dueDate    ", type any}, {"Task 2     ", type text}, {"Task 2 DueDate    ", type date}, {" Task 3     ", type text}, {"Task 3 DueDate   ", type date}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type2", {"Name    "}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Attribute] = "Task 1 dueDate    " or [Attribute] = "Task 2 DueDate    " or [Attribute] = "Task 3 DueDate   ")),
    #"Split Column by Position" = Table.SplitColumn(#"Filtered Rows", "Attribute", Splitter.SplitTextByRepeatedLengths(7), {"Attribute.1", "Attribute.2", "Attribute.3"}),
    #"Renamed Columns" = Table.RenameColumns(#"Split Column by Position",{{"Attribute.1", "Task"}, {"Value", "Due Date"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Attribute.2", "Attribute.3"})
in
    #"Removed Columns"

 

 

 If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!