Replicate Excel Pivot table in Query Editor

Hi Anonymous ,

Using below M codes:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUcoBYiNDXa/EPCDj0AJDIx0DAwMw08RSx9TEFCJqqWNgZqQUq4Oixy01CSwLggpmcH1GpjomhpYIGUsdM0NTdL2+iUUIFRZwvcYmOkZAi8B2GusYmpsrxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Cust = _t, Material = _t, #"Calendar Year/Month" = _t, Qty = _t, Sales = _t, Margin = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Cust", type text}, {"Material", type text}, {"Calendar Year/Month", type text}, {"Qty", Int64.Type}, {"Sales", Int64.Type}, {"Margin", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Calendar Year/Month"}, "Attribute", "Value"),
    #"Transposed Table" = Table.Transpose(#"Unpivoted Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"21-Jan", type text}, {"21-Jan_1", type text}, {"21-Jan_2", type any}, {"21-Jan_3", type any}, {"21-Jan_4", type any}, {"21-Feb", type text}, {"21-Feb_5", type text}, {"21-Feb_6", type any}, {"21-Feb_7", type any}, {"21-Feb_8", type any}, {"21-Mar", type text}, {"21-Mar_9", type text}, {"21-Mar_10", type any}, {"21-Mar_11", type any}, {"21-Mar_12", type any}})
in
    #"Changed Type1"

Then you will  get the pivot table you need:

You only modify the column names per your request.

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!