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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
NorthTexas
Regular Visitor

Multiple Groups within 1 Column Heading in Power Query

Need help in transforming excel dataset into pivot table ready format.  Original data file looks like this 

 

NorthTexas_0-1663203877440.png

Need to transform into pivot table table like this.

NorthTexas_1-1663203949483.png

 

Newer user to Power Query and struggling to get it there.  Any help would he appreciated.  Thought it would be esaeir but struggling. Thanks

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

You can group the products by defining a new custom column that copies the first column when the other columns are null in that row and returns null for the remaining rows that have numbers.

AlexisOlson_1-1663445944364.png

Fill down on that column to get:

AlexisOlson_2-1663445981507.png

From here, you can filter, unpivot, and pivot as needed.

 

Full sample query you can paste into the Advanced Editor of a new Blank Query to walk through the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKTylNLjFU0lGColgdoGhOYl5eakpwQWpeClDMzMAAhQQpcUwuKU3MUYArMbe0QKOQDFLwzC0oSi0uzszPKwZKGxuAABYGksGoOkyMTU2MjbAwwJZA/GA0DPxgPGT9EAsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Jan = _t, Feb = _t, Mar = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Jan", Int64.Type}, {"Feb", Int64.Type}, {"Mar", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Product", each if {[Jan],[Feb],[Mar]} = {null,null,null} then [Column1] else null, type text),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Product"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each [Column1] <> [Product]),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"Column1", "Product"}, "Month", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Columns", List.Distinct(#"Unpivoted Columns"[Column1]), "Column1", "Value")
in
    #"Pivoted Column"

View solution in original post

2 REPLIES 2
AlexisOlson
Super User
Super User

You can group the products by defining a new custom column that copies the first column when the other columns are null in that row and returns null for the remaining rows that have numbers.

AlexisOlson_1-1663445944364.png

Fill down on that column to get:

AlexisOlson_2-1663445981507.png

From here, you can filter, unpivot, and pivot as needed.

 

Full sample query you can paste into the Advanced Editor of a new Blank Query to walk through the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKTylNLjFU0lGColgdoGhOYl5eakpwQWpeClDMzMAAhQQpcUwuKU3MUYArMbe0QKOQDFLwzC0oSi0uzszPKwZKGxuAABYGksGoOkyMTU2MjbAwwJZA/GA0DPxgPGT9EAsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Jan = _t, Feb = _t, Mar = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Jan", Int64.Type}, {"Feb", Int64.Type}, {"Mar", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Product", each if {[Jan],[Feb],[Mar]} = {null,null,null} then [Column1] else null, type text),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Product"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each [Column1] <> [Product]),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"Column1", "Product"}, "Month", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Columns", List.Distinct(#"Unpivoted Columns"[Column1]), "Column1", "Value")
in
    #"Pivoted Column"
wdx223_Daniel
Super User
Super User

=let a=List.Skip(Table.ColumnNames(PreviousStepName)) in Table.Combine(Table.Group(PreviousStepName,a,{"n",each let t=Table.ToColumns(_) in Table.SelectColumns(#table({"Product","Month"}&List.Skip(t{0}),{List.Repeat({t{0}{0}},List.Count(t)-1),a}&List.Skip(t)),{"Product","Month","Actual Spend","Actual Impressions"})},0,(x,y)=>Byte.From(List.RemoveItems(Record.ToList(y),{null,""})={}))[n])

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors