Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
PowerBI_Query
Helper II
Helper II

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

unpivot1.jpg

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

1 ACCEPTED SOLUTION
smpa01
Super User
Super User

@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"
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

14 REPLIES 14
wdx223_Daniel
Super User
Super User

wdx223_Daniel_0-1635824127956.png

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

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?

 

unpivot8.jpg

 

 

yes,this is it.

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.

 

unpivot6.jpg

 

unpivot7.jpg

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.

unpivot4.jpg

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

unpivot5.jpg

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
Community Champion
Community Champion

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
Community Champion
Community Champion

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

Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

smpa01
Super User
Super User

@PowerBI_Query  provide your sample query here

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
smpa01
Super User
Super User

@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"
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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.

unpivot3.jpg

 

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.

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

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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?

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors