Convert Sheet1 into Sheet2 format using PQ

[alipezu]

Hi MarcelBeug and All,
I have a data on Sheet1 which I want to be look like sheet2. I have unpivoted all columns except the first one and Im stuck and can't go further ahead. Mr. MarcelBeug helped me in the past and I tried his steps here but didn't succeed. I think I am doing something wrong. Hope he or someone else will help me again.
Thanks.
https://drive.google.com/open?id=1lbutdGDn7Oixgy9GEXOm_5q_4MhDBUjI

 

[shift-del]

Alipezu,

here is my solution.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Merged Columns" = Table.CombineColumns(Source,{"October", "DATE"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"October.1"),
    #"Merged Columns1" = Table.CombineColumns(#"Merged Columns",{"November", "DATE2"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"November.1"),
    #"Merged Columns2" = Table.CombineColumns(#"Merged Columns1",{"December", "DATE3"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"December.1"),
    #"Merged Columns3" = Table.CombineColumns(#"Merged Columns2",{"January", "DATE4"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"January.1"),
    #"Merged Columns4" = Table.CombineColumns(#"Merged Columns3",{"Februay", "DATE5"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"February"),
    #"Merged Columns5" = Table.CombineColumns(#"Merged Columns4",{"March", "DATE6"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"March.1"),
    #"Merged Columns6" = Table.CombineColumns(#"Merged Columns5",{"April", "DATE7"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"April.1"),
    #"Merged Columns7" = Table.CombineColumns(#"Merged Columns6",{"May", "DATE8"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"May.1"),
    #"Merged Columns8" = Table.CombineColumns(#"Merged Columns7",{"June", "DATE9"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"June.1"),
    #"Merged Columns9" = Table.CombineColumns(#"Merged Columns8",{"July", "DATE10"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"July.1"),
    #"Merged Columns10" = Table.CombineColumns(#"Merged Columns9",{"August", "DATE11"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"August.1"),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Merged Columns10", {"Bus No."}, "Attribut", "Wert"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Wert", Splitter.SplitTextByEachDelimiter({";"}, QuoteStyle.Csv, false), {"Wert.1", "Wert.2"}),
    #"Filtered Rows" = Table.SelectRows(#"Split Column by Delimiter", each [Wert.2] <> null and [Wert.2] <> ""),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Attribut"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Wert.1", "Service type"}, {"Wert.2", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Bus No.", type text}, {"Service type", type text}, {"Date", type date}})
in
    #"Changed Type"

 

[MarcelBeug]

And here is my solution:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Bus No.", type text}, {"October", type text}, {"DATE", type date}, {"November", type text}, {"DATE2", type date}, {"December", type text}, {"DATE3", type date}, {"January", type text}, {"DATE4", type date}, {"Februay", type text}, {"DATE5", type date}, {"March", type text}, {"DATE6", type date}, {"April", type text}, {"DATE7", type date}, {"May", type text}, {"DATE8", type date}, {"June", type text}, {"DATE9", type date}, {"July", type text}, {"DATE10", type date}, {"August", type text}, {"DATE11", type date}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Bus No."}, "Attribute", "Value"),
    #"Added Index" = Table.AddIndexColumn(#"Unpivoted Other Columns", "Index", 0, 1),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index.1"},#"Added Index1",{"Index"},"Next",JoinKind.LeftOuter),
    #"Expanded Next" = Table.ExpandTableColumn(#"Merged Queries", "Next", {"Value"}, {"Next.Value"}),
    #"Removed Alternate Rows" = Table.AlternateRows(#"Expanded Next",1,1,1),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Alternate Rows",{"Attribute", "Index", "Index.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Value", "Service Type"}, {"Next.Value", "DATE"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Service Type", type text}, {"DATE", type date}})
in
    #"Changed Type1"

 

[shift-del]

MarcelBeug,

great trick: two index columns and merging the table against itself.

 

[MarcelBeug]

shift-del, yes it is worthwhile to have this in your Power Query trick portfolio.
It's just a few clicks to get data from different rows on one row.
And performance is much better compared to referencing rows with an Index value, as in: Source{Index}.

 

[alipezu]

Thank you MarcelBeug, you saved my time.. And also thanks to Shift-del for the help.

 

[billszysz]

My two pennies..... different approach.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Bus No."}, "Atrybut", "Service Type"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Atrybut"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Bus No."}, {{"tbl", each 
                          Table.FromColumns({List.Alternate(_[Service Type],1,1,1), List.Alternate(_[Service Type],1,1,0)}, {"Service Type", "Date"}), type table}}),
    #"Expanded {0}" = Table.ExpandTableColumn(#"Grouped Rows", "tbl", {"Service Type", "Date"}, {"Service Type", "Date"}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Expanded {0}", {{"Date", type date}}, "en-IE")
in
    #"Changed Type with Locale"