How do i perform pivot for a file that has 2 columns in row and one column in value

[madilee]

In my excel file, there are 3 columns with header name sku,location and carton id. I will normally pivot in excel by pulling sku then followed by location in row field and then carton id in value field.  The value field is to count the number of carton id. 

How do I do it in power query?

[smozgur]

1. Select the location column.
2. Click the Pivot Column button in the Transform ribbon tab.
3. Select carton_id as the Values Column.
4. Click OK.

Here is the sample M code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRSgRiQ6VYnWglI2MTIDsJiI2Q+CB5YzDf2MQUyE4GYhOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), {"sku", "location", "carton_id"}),
    ChangeTypes = Table.TransformColumnTypes(Source,{{"sku", type text}, {"location", type text}, {"carton_id", Int64.Type}}),
    
    PivotColumn = Table.Pivot(ChangeTypes, List.Distinct(ChangeTypes[sku]), "sku", "carton_id", List.Sum)
in
    PivotColumn

[madilee]

Hi Smozgur, 

Thank you for your reply. I am very new to Power Query, would appreciate if you could explain in more details.

Below is my file in excel:

After i pivot the file, below is the intended results:

This is how the layout is in pivot:

What do i do to get the same results in power query? 

Thank you. 

[Anonymous]

Hi @madilee ,

You can follow my steps to fix it.

1.Both select [Move From Loc] and [Sku] columns and right-click one of them to choose "Group By...".

2.Set up “Group By” as follows. The Total column will cont the ID group by [Move From Loc] and [Sku].

Best Regards,

Stephen Tao

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