Power Query Re-Shaping Data

Hi,

I have data in A1:Q7 that I would like to re-shape to look like that in A10 to A16.

I want to use Power Query to do this.

Can someone please suggest the M Code to achieve this? Ideally the solution will include a dynamic re-ordering of the columns rather than a drag and drop in the PQ editor. The reason for this is that this is made-up data for this query. The real data covers dozens of dates.

Can someone please suggest a Power Query solution?

Thanks!

Is the date in Column M correct. If yes, then this causes some issues with Power Query. How important is the sort of Line two to be as shown with Weight first, etc. If the date in M is incorrect and the sort order other than date sequence is not significant, I think I can make this work in Power Query. However, if the order of data is critical, then someone else may be able to help as that is above my skill level.

Before I start, will you answer my questions in the post regarding the horizontal sorts.

Look at this solution. I think that I have what you are looking for.

PHP Code: 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Demoted Headers" = Table.DemoteHeaders(Source),
    #"Transposed Table" = Table.Transpose(#"Demoted Headers"),
    #"Added Custom" = Table.AddColumn(#"Transposed Table", "Custom", each if Text.StartsWith([Column1],"Column") then null else [Column1]),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Custom", "Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Column1"}),
    #"Filled Down" = Table.FillDown(#"Removed Columns",{"Custom"}),
    #"Filled Up" = Table.FillUp(#"Filled Down",{"Custom"}),
    #"Merged Columns" = Table.CombineColumns(#"Filled Up",{"Custom", "Column2"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
    #"Transposed Table1" = Table.Transpose(#"Merged Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"1/1/2025:CODE", "CODE"}, {"1/1/2025:NAME", "NAME"}}),
    #"Filtered Rows1" = Table.SelectRows(#"Renamed Columns", each ([CODE] <> "TOTAL")),
    ColumnsToKeep = List.Select(Table.ColumnNames(#"Filtered Rows1"), each Text.Contains(_, "CODE") or Text.Contains(_, "NAME") or Text.Contains(_, "WEIGHT") ),
    Unpivoted = Table.UnpivotOtherColumns(#"Filtered Rows1", ColumnsToKeep, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(Unpivoted, List.Distinct(Unpivoted[Attribute]), "Attribute", "Value", List.Sum)
in
   #"Pivoted Column" 


Thanks for the rep.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.