Need help pivoting or unpivot/ reshaping table

[Moses_Lau]

Hi there, any guidance will be appreciated!

Thanks.

[spinfuzer · Accepted solution]

Import Table without Headers.  Include the years in the first row.  Column names should be Column1 to ColumnN.

1) Transpose

2) Fill Down on Column with Year

3) Merge Column1 and Column2 with no delimiter

4) Transpose

5) Promote Headers

6) Unpivot Other columns: Country and Company

7) Split Column the Attribute Column to Digit to non digit

😎Pivot on Attribute.2 column and Values Column = [Value]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtIBISMDIyM4yxjOMgGxYnWilZzzS/NKiiqBXOf83ILEPBArODEntRhIBxTlp2WWECEAMsgRyHUEEYZQbATFxmAMU+LkRFCJszNuJbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8"}),
    #"Transposed Table" = Table.Transpose(#"Replaced Value"),
    #"Filled Down" = Table.FillDown(#"Transposed Table",{"Column1"}),
    #"Merged Columns" = Table.CombineColumns(#"Filled Down",{"Column1", "Column2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    #"Transposed Table1" = Table.Transpose(#"Merged Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Country", type text}, {"Company", type text}, {"2022Sales", Int64.Type}, {"2022Profit", Int64.Type}, {"2023Sales", Int64.Type}, {"2023Profit", Int64.Type}, {"2024Sales", Int64.Type}, {"2024Profit", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Country", "Company"}, "Attribute", "Value"),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Attribute.1", "Attribute.2"}),
    #"Pivoted Column" = Table.Pivot(#"Split Column by Character Transition", List.Distinct(#"Split Column by Character Transition"[Attribute.2]), "Attribute.2", "Value", List.Sum),
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Attribute.1", "Year"}})
in
    #"Renamed Columns"

[Moses_Lau]

You are a saviour, worked the way I wanted it to, thanks a lot!!

I have a lot to learn.

[BA_Pete]

Hi @Moses_Lau ,

Please import your 'Original view on Excel' example table into Power Query, select a query step before any transformations have been performed, select the icon at the top-left of the table and choose 'Copy Entire Table'.

Now go to the Home tab > Enter Data and paste this table in there and hit OK.

Once this has loaded as a new query, go to the Home tab > Advanced Editor and copy ALL of the code in there and paste it into a code window ( </>) button here.

This will allow me to see the EXACT format that you're starting from in order to create your desired outcome.

Pete