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

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?

Need Help

ZhangKun

1 year ago

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.

Anonymous

1 year ago

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
1 year ago
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

9 Replies

ZhangKun
Super User
1 year ago
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
  New Member
  1 year ago
  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.
lbendlin
Super User
1 year ago
Your expected output doesn't seem to map to your sample data. Please clarify the logic you are applying.
- Royle79
  New Member
  1 year ago
  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
    Super User
    1 year ago
    Please provide sample data that fully covers your issue.
    Please show the expected outcome based on the sample data you provided.
Krishana_123
Frequent Visitor
1 year ago
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

Forum Discussion

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

9 Replies

Related content

Help Transposing

Population dynamic graaph

Transpose data

count distinct

Transpose Title Row ONLY