Multiple Groups within 1 Column Heading in Power Query

[NorthTexas]

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

Need to transform into pivot table table like this.

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

[AlexisOlson · Accepted solution]

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.

Fill down on that column to get:

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]

=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])