Stacking Columns / Shifting metrics into columns

[LERnew88]

Hello,

I am combining multiple sources of data, but one particular file is giving me trouble. All of my sales metrics and months are spread across the top columns (Year/Month and each particular metric in each column with the data populated below). 

Is there a way to easily transform the data into 4 columns (one more month, one for year, one for the sales metric itself, and one for the value)?

[v-yanjiang-msft · Accepted solution]

Hi @LERnew88 ,

According to your description, I create a sample, here's my solution.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nY5PD8FAEMW/StMzYqerOC7aXhSxHKQRKdmIxL+0fH/zptmeXDjs5O1v3syboghX1/Ie3NyrupzqsBNulpuundtDVj3qOrDl1f1LF+71I9t3ipAb1KeolyaTbrqjKEjd0bPcrIXlZeURLzFzgZ6YbdYMmvfZM5usGmbd88skcg1zxW/Ebww9gJG4REMxTPD3TThVDILcaCyOKSQafRRRQ1g0F63EMoNsFyBCIZCgdHNIwlIa5BcoRBLS9EAsKcu4zZAuIglm3VybsZRZpJFqC9I0n7v/AA==", 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]),
    #"Promoted Headers" = Table.PromoteHeaders(Table.Skip(Source,1), [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"2023.FEB-FY23 Feb", Int64.Type}, {"2023.MAR-FY23Mar", Int64.Type}, {"2023.TOTAL-FY23", Int64.Type}, {"2023.AUG-FY23 Aug", Int64.Type}, {"2023.SEP-FY23 Sep", Int64.Type}, {"2023.TOTAL-FY23_1", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"", "2023.AUG-FY23 Aug", "2023.SEP-FY23 Sep", "2023.TOTAL-FY23_1"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Custom", each "TOT-SLS_GrossSales"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"2023.AUG-FY23 Aug", "2023.SEP-FY23 Sep", "2023.TOTAL-FY23_1"}),
    #"Unpivoted Columns2" = Table.UnpivotOtherColumns(#"Changed Type", {"", "2023.FEB-FY23 Feb", "2023.MAR-FY23Mar", "2023.TOTAL-FY23"}, "Attribute", "Value"),
    #"Added Custom1" = Table.AddColumn(#"Unpivoted Columns2", "Custom", each "NET-SLS_NetSales"),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"2023.FEB-FY23 Feb", "2023.MAR-FY23Mar", "2023.TOTAL-FY23"}),
    #"Appended Query" = Table.Combine({#"Removed Columns", #"Removed Columns1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Appended Query",{{"", "Plan metrics"}, {"Attribute", "Year"}, {"Custom", "Sales metrics"}})
in
    #"Renamed Columns"

I attach the file below for your reference.

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

Best regards,

Community Support Team_yanjiang