Forum Discussion

PowerBI_Query's avatar
PowerBI_Query
Helper II
4 years ago
Solved

Unpivot non-consecutive columns

I am able to unpivot consecutive columns by tweaking the M code but having trouble when unpivoting non-consecutive columns as shown below. I need to make the column names dynamic. So that when new da...
Show and Tell
Tips and Tricks
  • smpa01's avatar
    smpa01
    4 years ago

    PowerBI_Query  can you try this

     

    let
    Source = Excel.Workbook(File.Contents("C:\Users\user1\Desktop\Unpivot.xlsx"), null, true),
    #"Before unpivot_Sheet" = Source{[Item="Before unpivot",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"Before unpivot_Sheet", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Category", type text}, {"Sub Category", type text}, {"MML 01 Hrs", Int64.Type}, {"MML 01 $", Int64.Type}, {"MML 02 Hrs", Int64.Type}, {"MML 02 $", Int64.Type}, {"MML 03 Hrs", Int64.Type}, {"MML 03 $", Int64.Type}, {"MML 04 Hrs", Int64.Type}, {"MML 04 $", Int64.Type}}),
    Custom1 = Table.DemoteHeaders(#"Changed Type"),
    #"Changed Type1" = Table.TransformColumnTypes(Custom1,{{"Column1", type text}, {"Column2", type text}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type1"),
    #"Filtered Rows" = Table.SelectRows(#"Transposed Table", each ([Column1] = "Category" or [Column1] = "Sub Category") or Text.Contains([Column1], "Hrs")),
    #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows","Hrs","",Replacer.ReplaceText,{"Column1"}),
    #"Transposed Table1" = Table.Transpose(#"Replaced Value"),
    #"Promoted Headers1" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Unpivoted Other Columns" = Table.Distinct(Table.UnpivotOtherColumns(#"Promoted Headers1", {"Category", "Sub Category"}, "MML", "HRS")),
    Custom2 = Table.SelectRows(#"Transposed Table", each ([Column1] = "Category" or [Column1] = "Sub Category") or Text.Contains([Column1], "$")),
    #"Replaced Value1" = Table.ReplaceValue(Custom2,"$","",Replacer.ReplaceText,{"Column1"}),
    #"Transposed Table2" = Table.Transpose(#"Replaced Value1"),
    #"Promoted Headers2" = Table.PromoteHeaders(#"Transposed Table2", [PromoteAllScalars=true]),
    #"Unpivoted Other Columns1" = Table.Distinct(Table.UnpivotOtherColumns(#"Promoted Headers2", {"Category", "Sub Category"}, "MML", "$")),
    #"Merged Queries" = Table.NestedJoin(#"Unpivoted Other Columns", {"Category", "Sub Category", "MML"}, #"Unpivoted Other Columns1", {"Category", "Sub Category", "MML"}, "Unpivoted Other Columns1", JoinKind.LeftOuter),
    #"Expanded Unpivoted Other Columns1" = Table.ExpandTableColumn(#"Merged Queries", "Unpivoted Other Columns1", {"$"}, {"$"})
in
    #"Expanded Unpivoted Other Columns1"
ronrsnfld's avatar
ronrsnfld
Super User
4 years ago

If you do not need the duplicate rows that are in your sample workbook, then try this:

 

If you step through the applied steps and read the comments, the algorithm should be clear.  Let me know if you have questions.

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

//Remove duplicate rows
    dedup = Table.Distinct(Source),

//set datatypes
    typeIt = Table.TransformColumnTypes(dedup,
        {{"Category", type text},{"Sub Category", type text}} & 
        List.Transform(List.RemoveFirstN(Table.ColumnNames(dedup),2),each {_, Int64.Type})),

//Unpivot all except first two columns
    unPivot = Table.UnpivotOtherColumns(typeIt,{"Category","Sub Category"},"MML","Value"),

//Split the attribute column by last space to produce column to pivot on
    #"Split Column by Delimiter" = Table.SplitColumn(unPivot, "MML", 
        Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"MML", "MML.2"}),

//Pivot on attribute column with NO aggregation
    #"Pivoted Column" = Table.Pivot(#"Split Column by Delimiter", 
        List.Distinct(#"Split Column by Delimiter"[MML.2]), "MML.2", "Value"),

//Split Sub Category column to allow natural sort
//Then sort and remerge the column
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Pivoted Column", "Sub Category", 
        Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.Csv, true), {"Sub Category.1", "Sub Category.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Sub Category.1", type text}, {"Sub Category.2", Int64.Type}, {"MML", type text}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"MML", Order.Ascending}, {"Sub Category.1", Order.Ascending}, {"Sub Category.2", Order.Ascending}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Sorted Rows", 
        {{"Sub Category.2", type text}}, "en-US"),{"Sub Category.1", "Sub Category.2"},
        Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Sub Category")
in
    #"Merged Columns"