Unpivot non-consecutive columns

[PowerBI_Query]

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 data is added to the right it gets updated after refresh.

I need to unpivot both green and red highligted columns into two columns. As the new data gets added to the right it should update after the refresh.  Sample file link

[wdx223_Daniel]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Custom1 = let col= List.Transform(List.Alternate(List.Skip(Table.ColumnNames(Source),2),1,1),each Text.Replace(_," $","")) in #table(List.FirstN(Table.ColumnNames(Source),2)&{"MML","HRS","$"},List.TransformMany(Table.ToRows(Source),each List.Zip({List.Split(List.Skip(_,2),2),col}),(x,y)=>List.FirstN(x,2)&{y{1}}&y{0}))
in
    Custom1

[PowerBI_Query]

This is how I visualize it could you confirm if this is right? Zip creates 4 lists in a list for each list in Table.ToRows step. (x,2), y{0} and y{1} did I get them right?

[wdx223_Daniel]

yes，this is it.

[PowerBI_Query]

I managed to understad most of it except the List.TransformMany funtion. I get errors in this step could you explain the last step? how resultTransform argument works.

[PowerBI_Query]

I have been trying to understand the query by breaking down into small steps.

However, I am unable to undestand the nested let funtion significance. If you don't mind could you explain it?

Custom1 step has filtered MML 1 MML 2 MML 3 MML 4 dynamically then a table is created with the headings from List.FirstN and concating {"MML","HRS","$"}

But why #table after in? 

What is the pagesize in List.Spilt mean and List.Zip has no arguments so how col was refered in List.Zip({List.Split(List.Skip(_,2),2),col})

and the user define funtion after the go to operator (x,y)=>List.FirstN(x,2)&{y{1}}&y{0}))
Do 0, 1 and 2 refer to positions I guess. But how do I visulize it? I get it that the values are being placed under the table headings. How does the new table get filled, I did not understand.

I could not perform List.Zip steps on Table.ToRows could you spilt the original query into small steps as below?

[PowerBI_Query]

It worked in excel.
How do I update the file path in Power BI? in the below step "C:\Power BI\Unpivot.xlsx"

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

[ronrsnfld]

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"

[CNENFRNL]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZS7bhwxDEV/ZbH1BpAo6lXnE1waWzhAkNKA4RT5+8w9HK/tgWFtMYJEjY7IS1KPj+efT6+//zy//Dvl8+X88PfX6d3wQ6ac6zaWNpIWzbax5uFa9LGNbt3YkanM0s7Xy/dU/e4APImQGbCk/ctDP3XbLl3Ryu3yXHJ40nVBgl0SHI2WNI6U1kz/7MJ2lnkKuw7YmA0lZDHzJbJyCD3xZsrJMkJVpCsCKhofS1rDkUmAhldZJm+Ot9qYcRvU6r4OWg7lBMbA5N5jAUgKEDO5b1vSV8CB3PoZXNfhFhnv8rD2cHFmdu9wUacoRw/dPkwNfQPOvNe1h1lHqyh7Ior0r4UMFd3WUNdtT9AnoB2BRsO0wfUW/UKR0DTRMhEy9dpqS0uiTKNDjKbbBdyzS4YKO5PtmnzJLFG0VAqcMm6pGVHRZJraGW0N9Ns7sb0MxD0lnvcehHAVJI3UxhIp2OTlSUHER/ccpT/eYtWC3pp3iEk2wjNeihQFHkWKZYbIxamDO5B0DWr52/sA1HHRNVclytuCpMfG+YI5jvnZIsXEC6YERZuHImstiTh6p0XZyBBKGvNpt36yYx9+VebpICTiWZ27hrBQkhJSX16v/wE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, #"Sub Category" = _t, #"MML 01 Hrs" = _t, #"MML 01 $" = _t, #"MML 02 Hrs" = _t, #"MML 02 $" = _t, #"MML 03 Hrs" = _t, #"MML 03 $" = _t, #"MML 04 Hrs" = _t, #"MML 04 $" = _t]),
    #"Demoted Headers" = Table.DemoteHeaders(Source),
    Cols = Table.ToColumns(#"Demoted Headers"),
    #"List of Tables" = let fixed = List.FirstN(Cols,2) in List.Transform(List.Split(List.Skip(Cols,2),2), each Table.PromoteHeaders(Table.FromColumns(fixed & _))),
    #"Table Transformation" = let colNames = List.FirstN(Table.ColumnNames(Source),2) in List.Transform(#"List of Tables", each Table.SplitColumn(Table.UnpivotOtherColumns(_, colNames, "attr", "val"), "attr", (col) => Splitter.SplitTextByPositions({0, Text.PositionOf(col, " ", Occurrence.Last)+1})(col), {"MML", "a"})),
    Combined = Table.Combine(List.Transform(#"Table Transformation", each Table.Pivot(_, List.Distinct([a]), "a", "val")))
in
    Combined

[smpa01]

@PowerBI_Query  provide your sample query here

[smpa01 · Accepted solution]

@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"

[PowerBI_Query]

So merge is the only way to pivot those two colums side by side. And we can merge the same table with it but the OK is greyed out in the join window.

Before I accept it as solution. 

I did in a similar way but had to duplicate query then do inner join.

What I don't understand is how does the solution where deleting $ columns first then unpivoting it and deleting Hrs columns next then unpivoting it. Later merge both tables work when new data (MML 05 Hrs MML 05 $) is added to it.

I can see the column headings are hardcoded in the code however, when new data is added to the right and refreshed it works perfectly I don't understand how? MML 05 Hrs and MML 05 $ are removed and unpivoted.

[smpa01]

@PowerBI_Query  you really don't need to duplicate query (one for Hrs and one for $). PQ can self join any previous steps and that is what I did. 

[smpa01]

@PowerBI_Query  I can see the column headings are hardcoded in the code however, when new data is added to the right and refreshed it works perfectly I don't understand how? 

- we know there are columns which will be constant axis (Category, Sub Category). 

In

#"Filtered Rows"= Table.SelectRows(#"Transposed Table", each ([Column1] = "Category" or [Column1] = "Sub Category") or Text.Contains([Column1], "Hrs"))

and 

Custom2= Table.SelectRows(#"Transposed Table", each ([Column1] = "Category" or [Column1] = "Sub Category") or Text.Contains([Column1], "$"))

only Column1 and Column2 is Harcoded (cause those are our constant axis) but the other elemets are not, SO whenever new data gets added, it will get correctly filtered dynamically through Text.Contains([Column1], "Hrs" and Text.Contains([Column1], "$").

I don't think you can unpivot data on different partition (Hrs, $) i the same step at once. Therefore, unpivoted speratly after dynamic filtering and merged back.

[PowerBI_Query]

Hardcoding was related to my solution where I first duplicate the query. In $ query I remove Hrs and unpivot other columns.

In Hrs query, I remove $ columns and unpivot other columns, doing this way woud hardcode {MML 01 $ MML 02 $ MML 03 $ MML 04 $} and {MML 01 Hrs MML 02 Hrs MML 03 Hrs MML 04 Hrs} 

Then I merge $ query and Hrs query. Now when I add new data MML 05 Hrs and MML 05 $ it still works I don't understand how?