How to create excel pivot table in power query

Hi Rahul,

Let me know if this is what you are looking for:

Outcome

Step 1: GroupBy

Table.Group(#"Promoted Headers", {"SuperRegion"}, {{"1", each Table.PrefixColumns(Table.FirstN(Table.PromoteHeaders(Table.Transpose(Table.DemoteHeaders(Table.RemoveColumns(_, {"SuperRegion"})))),1), "CallCount")}, {"2", each Table.PrefixColumns(Table.LastN(Table.PromoteHeaders(Table.Transpose(Table.DemoteHeaders(Table.RemoveColumns(_, {"SuperRegion"})))),1), "TotalTEU")}})

Step 2 and 3:

Expand the columns

Regards

KT

Hi Rahul_SC 

It is recommended to keep the table a flat table in Power Query rather than pivot it. This is usually more friendly for the downstream calculation and report design in Power BI or somewhere else. 

However, if you want to pivot it, you can try the pivot/unpivot feature in Power Query. First select last two columns and click on Unpivot Columns to unpivot them. 

Add a custom column to concatenate AbsoluteStart_Month and Attribute columns. 

Remove AbsoluteStart_Month and Attribute columns. Select the custom column and pivot it. When pivoting, select Value column as the Values. 

You will get the following result then. Actually this is still a flat table as Power Query only supports flat tables. It cannot hold multiple values under one column value in Power Query Editor. 

Full M code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc49CoAwDAXgu3Tu0NTmb3TwFOIgouCkVLy/TRURXPKG94Wk71275HUanXcgIVpgKlMiJ4xu8AUc61M3n1oBqNbdmbd9vkGqQA0gMMu9/zmAFmRAgwbAHwALhgqYQH+AXiBCzOXF4QI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SuperRegion = _t, AbsoluteStart_Month = _t, CallCount = _t, TotalTEU = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"SuperRegion", type text}, {"AbsoluteStart_Month", Int64.Type}, {"CallCount", Int64.Type}, {"TotalTEU", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"SuperRegion", "AbsoluteStart_Month"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Custom", each Text.From([AbsoluteStart_Month]) & [Attribute]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"AbsoluteStart_Month", "Attribute"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom]), "Custom", "Value", List.Sum)
in
    #"Pivoted Column"

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.