Transpose Column A as Column header and populate rows with Column B distinct

[Royle79]

Hi All,

I have 2 columns of data, column A is a list of People and column B are Regions where said person operates. So where "Name One" is the Person, Region 1, Region 2, etc are the places of operation.

Person	Place
Name 1	Place 1
Name 1	Place 2
Name 1	Place 3
Name 2	Place 3
Name 2	Place 4
Name 3	Place 5
Name 4	Place 1
Name 5	Place 4

When I transpose in PQE i am getting columns for each occurance of the Person ("Name One", "Name One_1", etc) for each of my places in B.

I am looking for a Power Query Solution as I have to create a number of dependant lists along the same condition. My final Output should look something like;

Name 1	Name 2	Name 3	Name 4	Name 5
Place 1	Place 1	Place 1	Place 3	Place 1
Place 2		Place 2	Place 4	Place 2
Place 3			Place 5	Place 3
				Place 4
				Place 5

This is as close to the solution as I can get.

Any help would be appreciate.

Thanks.

Can this be acheived in PQE?

[ZhangKun · Accepted solution]

I'm not sure if your source data is incorrect or I don't understand the calculation logic. My final query is as follows:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kvMTVUwVNJRCshJTAaxYnUwBI2wCRojBI3wC5ogBI3hgqYIQRNstpsia48FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Person = _t, Place = _t]),
    GroupByPerson = Table.Group(Source, {"Person"}, {{"G", each [Place], Int64.Type}}),
    result = Table.FromRows(List.Zip(List.Transform(GroupByPerson[G], List.Distinct)), GroupByPerson[Person])
in
    result

If you "pivot" the Person column, you'll need to do something similar, where List.Zip is used to align the data and automatically fill in nulls.

[v-cgao-msft · Accepted solution]

Hi @Royle79 ,

May I ask if you have tried ZhangKun's code? You can create a new blank query and paste the code into the advanced editor.
Provide another approach.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZPBboMwDIZfxcqZSgkpjB6XrWiHbkVD26XjEI3QZWKJFODQt5+pxNqyrYSb//j/HMeG3Y5kyjXWACMByWr5ro6R6HRdarOHmwsBj1m+IUUwRTEKCxAZbHWNx2mnakil+/Ih+ws30pTw1lEaxjBkGkidNW3veZVm30lXwrOV5ayasvzErGnhPOdRYDVS00gYonoxum3gbnjK/RwOxCz32sPN6Uh5IGxonsd4/mRd+6GcgTUO01YgTnmBBo964Uj9izzgkqUDHvUxj/B7yhGpFeStdergDfo0lYzUGcL/RuLkZ723AYhg3pLj1bA2Nst9ZVy/3Q3woafl5LQSehFPX5MclTw0wHA1Po2dCI5E5DFkRvsfd1tVGjOcFMU3", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Person = _t, Place = _t, #"Place 2" = _t, #"Place 3" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Person", type text}, {"Place", type text}, {"Place 2", type text}, {"Place 3", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Person"}, {{"Place", each List.Distinct(_[Place])}}),
    Custom1 = Table.FromColumns(#"Grouped Rows"[Place],#"Grouped Rows"[Person])
in
    Custom1

With the given example data, it returns the result:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Krishana_123 · Accepted solution]

This is what you want if I understand correct. Below is the solution for that please accept it as solution if that helps 

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

#"Changed Type" = Table.TransformColumnTypes(Source,{{"Person ", type text}, {"Region", type text}}),

#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Person ", "Name"}}),

IndexedTable = Table.AddIndexColumn(#"Renamed Columns", "Index", 1, 1, Int64.Type),

PivotedTable = Table.Pivot(IndexedTable, List.Distinct(IndexedTable[Name]), "Name", "Region"),
Custom1 = Table.FromColumns(
List.Transform(
Table.ToColumns(PivotedTable),
(col) => List.Sort(
col,
(a, b) => if a = null then 1 else if b = null then -1 else Comparer.Ordinal(a, b)
)
),
Table.ColumnNames(PivotedTable)
)
in
Custom1

[lbendlin]

Your expected output doesn't seem to map to your sample data.  Please clarify the logic you are applying.

[Royle79]

Hi,

Thanks for the message. Apologies for confusing matters. The output image details the specifics, so what you see as the headers are the names referred to as Name 1, Name 2 etc.

The final aim of this exercise is to use the process to create heirachy lists, the lists grow, as the does the header count. Eventually I will require approx 60 lists, all dependant on the option chosen from another list.

Person 1 = 4 places

Place 1 = 7 Subs

Sub 1 = 25 Subs2

etc.

I thought if I could pivot 2 columns at a time, using column 1 create the headers and column 2 as the values to fill those new columns.

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[Royle79]

Here is the data;

Person	Place	Place 2	Place 3
Person 1	Place 1	Building 7	Building 7 MPSL
Person 1	Place 1	Building 10 - BP Oil	Fuel Farm
Person 1	Place 1	Building 17	Land & Buildings Fronting Vanguard Road
Person 1	Place 1	Building 17	Land adjacent Building 17
Person 1	Place 1	Building 19	Building 19
Person 1	Place 1	Building 22	Units C & D
Person 1	Place 1	Building 22	Unit B
Person 1	Place 1	Building 22	Unit E
Person 1	Place 1	Building 30	Building 30
Person 1	Place 1	Building 31 & 36	Northern End of B31 & B36
Person 1	Place 1	Building 32	Building 32
Person 1	Place 1	Hangar 35	H35 - Single Storey
Person 1	Place 1	Hangar 35	H35
Person 1	Place 1	Building 38	Building 38
Person 3	Place 1	Building 68	Units A, B, C & D
Person 1	Place 1	Building 69	Unit 1
Person 1	Place 1	Building 69	Unit 2
Person 1	Place 1	Building 69	Units 3 & 4
Person 1	Place 1	Hangar 80	Hangar 80
Person 1	Place 1	Building 81	Bays 1 - 2
Person 1	Place 1	Building 81	Bays 3 - 5
Person 3	Place 1	Building 107	Office 3

The desired outcome;

Person 1	Person 2	Person 3
Place 1	Place 1	Place 1
Place 2		Place 3
Place 3

I want to create columns based on "Person" and populate it with matching data from "Place". This is to essentially create a set of dynaminc and dependant lists.

Thanks.

[v-cgao-msft · Accepted solution]

Hi @Royle79 ,

May I ask if you have tried ZhangKun's code? You can create a new blank query and paste the code into the advanced editor.
Provide another approach.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZPBboMwDIZfxcqZSgkpjB6XrWiHbkVD26XjEI3QZWKJFODQt5+pxNqyrYSb//j/HMeG3Y5kyjXWACMByWr5ro6R6HRdarOHmwsBj1m+IUUwRTEKCxAZbHWNx2mnakil+/Ih+ws30pTw1lEaxjBkGkidNW3veZVm30lXwrOV5ayasvzErGnhPOdRYDVS00gYonoxum3gbnjK/RwOxCz32sPN6Uh5IGxonsd4/mRd+6GcgTUO01YgTnmBBo964Uj9izzgkqUDHvUxj/B7yhGpFeStdergDfo0lYzUGcL/RuLkZ723AYhg3pLj1bA2Nst9ZVy/3Q3woafl5LQSehFPX5MclTw0wHA1Po2dCI5E5DFkRvsfd1tVGjOcFMU3", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Person = _t, Place = _t, #"Place 2" = _t, #"Place 3" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Person", type text}, {"Place", type text}, {"Place 2", type text}, {"Place 3", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Person"}, {{"Place", each List.Distinct(_[Place])}}),
    Custom1 = Table.FromColumns(#"Grouped Rows"[Place],#"Grouped Rows"[Person])
in
    Custom1

With the given example data, it returns the result:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[lbendlin]

nowhere in your sample data does it say "Person2" ?

[ZhangKun · Accepted solution]

I'm not sure if your source data is incorrect or I don't understand the calculation logic. My final query is as follows:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kvMTVUwVNJRCshJTAaxYnUwBI2wCRojBI3wC5ogBI3hgqYIQRNstpsia48FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Person = _t, Place = _t]),
    GroupByPerson = Table.Group(Source, {"Person"}, {{"G", each [Place], Int64.Type}}),
    result = Table.FromRows(List.Zip(List.Transform(GroupByPerson[G], List.Distinct)), GroupByPerson[Person])
in
    result

If you "pivot" the Person column, you'll need to do something similar, where List.Zip is used to align the data and automatically fill in nulls.

[Royle79]

Many thanks for this. Admittidly I have not ran this code as there are elements I don't understand, purely due to a lack of knowledge on JSON code. I found a simpler solution using excel functions UNIQUE & FILTER for what I needed (dynamic dropdown lists), but it is a specific fix for the project I am working on rather than a full solution to the issue I had. When I have some time to learn, test and develop I will certainly try this code. I really appreciate your input and effort in helping me find the solution.