How to create excel pivot table in power query

[Rahul_SC]

Hi @KT_Bsmart2gethe , and all,

Is it possible to create pivot table in power query ? like below, I have a long list of data, what I want to do is create a pivot table using it. In excel, I put, super region in row field, AbsoluteStart_Month in column feild then in value feild last two columns.

Group by option can help but in column field, how can I bring 2nd column in Column field option that we can see in excel pivot table or in Power bi Matrix table ?

SuperRegion	AbsoluteStart_Month	CallCount	TotalTEU
Africa	1802	154	827452
Asia	1803	154	829116
Europe	1804	159	851778
Africa	1805	169	909015
Africa	1801	171	907619
Africa	1806	171	886772

[KT_Bsmart2gethe · Accepted solution]

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

[Rahul_SC]

@KT_Bsmart2gethe @v-jingzhang , both the solutions work. Thanks so much!

[v-jingzhang · Accepted solution]

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.

[KT_Bsmart2gethe · Accepted solution]

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