Pivot/unpivot help

[tombamber]

Hi

Using PowerQuery I want to transform the first table into the second so that every 'Number' in a 'Region' shows every 'Location' in that 'Region', with multiple rows per 'Number'. This is just dummy data.

I have tried doing this with pivot/unpivot but struggling to get my head around it.

Transform this...

...into this...

Thanks

 

[RoryA]

Load the data in to two queries. In the first one remove the Number column then group on the other two. In the second, remove the location column (it doesn't look like you have duplicate numbers, but if you do, group the columns). Then merge the two queries on Region, and expand the resulting table column.

 

[JGordon11]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tbl = Table.AddColumn(Source, "Custom", each Table.SelectRows(Source, (x)=> x[Region] = [Region])[Location]),
    tbl1 = Table.RemoveColumns(tbl,{"Location"}),
    tbl2 = Table.ExpandListColumn(tbl1, "Custom"),
    tbl3 = Table.RenameColumns(tbl2,{{"Custom", "Location"}}),
    tbl4 = Table.Distinct(tbl3),
    Result = Table.ReorderColumns(tbl4,{"Number", "Region", "Location"})
in
    Result

Book1
	A	B	C	D	E	F	G	H
1	Location	Region	Number		Number	Region	Location
2	1234	Region 1	362559		362559	Region 1	1234
3	1234	Region 1	625223		362559	Region 1	4537
4	4537	Region 1	538346		625223	Region 1	1234
5	5467	Region 2	348920		625223	Region 1	4537
6	6544	Region 2	779436		538346	Region 1	1234
7	5467	Region 2	281464		538346	Region 1	4537
8	4355	Region 3	862489		348920	Region 2	5467
9	4355	Region 3	266184		348920	Region 2	6544
10	6799	Region 3	396368		779436	Region 2	5467
11					779436	Region 2	6544
12					281464	Region 2	5467
13					281464	Region 2	6544
14					862489	Region 3	4355
15					862489	Region 3	6799
16					266184	Region 3	4355
17					266184	Region 3	6799
18					396368	Region 3	4355
19					396368	Region 3	6799
20
Sheet1

 

[GraH]

a UI based solution (same concept as already mentioned above).

let
    Source = Excel.CurrentWorkbook(){[Name="Locations"]}[Content],
    #"Merged Queries" = Table.NestedJoin(Source, {"Region"}, Source, {"Region"}, "Locations", JoinKind.LeftOuter),
    #"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"Location"}),
    #"Expanded Changed Type" = Table.ExpandTableColumn(#"Removed Columns", "Locations", {"Location"}, {"Location"}),
    #"Removed Duplicates" = Table.Distinct(#"Expanded Changed Type"),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Duplicates",{"Number", "Region", "Location"})
in
    #"Reordered Columns"

 

[tombamber]

a UI based solution (same concept as already mentioned above).

let
    Source = Excel.CurrentWorkbook(){[Name="Locations"]}[Content],
    #"Merged Queries" = Table.NestedJoin(Source, {"Region"}, Source, {"Region"}, "Locations", JoinKind.LeftOuter),
    #"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"Location"}),
    #"Expanded Changed Type" = Table.ExpandTableColumn(#"Removed Columns", "Locations", {"Location"}, {"Location"}),
    #"Removed Duplicates" = Table.Distinct(#"Expanded Changed Type"),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Duplicates",{"Number", "Region", "Location"})
in
    #"Reordered Columns"

Thanks, a UI version would be great for my level of experience.

Can you elaborate a little on how to do this as I don't fully understand the code.

 

[GraH]

On the Data Ribbon -> Get data from Table/range

In the PQ UI, on the home tab, Merge Queries, and combine the query with itself. Select Region in "both" tables. In the Join type go for left Outer, the default.

In the result at this stage, select the column Location and press the delete button on your keyboard.

In the column Locations, click on the icon with two arrows pointing up and to the side (that's the expand button)

Select Location and press OK.

Again from the home tab, in the Remove Rows drop down, select Remove Duplicates.

Reorganizing the columns is much like in excel. You select it with a left mouse click and move it around while holding the left mouse button. Release it when the column in the correct order.

Finally it's time to close and load the data to excel. Either as a pivot table or a table. You probably do not need to use the data model at all.