Excel Forum

how to transpose a particular column values that tie to userid into Cross table (pivot) in Power Query

Chris Yap

Member

Members

Forum Posts: 162

Member Since:
August 21, 2019

Offline

August 21, 2019 - 1:19 pm

Hi all power query guru

Please see my source worksheet, each Userid will have many User group ID, need to transform into a cross table (see the output in Source worksheet, the second table)

in the end we are interested with user id that holding User\Domain users and Everyone, and if it matches, ED = Yes, else ED = No

Can power query UI transform it or require this to be done using advanced editor

I am quite new in PQ, appreciate your guidance

Thank you !

Note:- of course I can use native excel to do it using arrays etc.

See Answer

Catalin Bombea

Iasi, Romania

Member

Dashboards

Power Query

Power Pivot

Xtreme Pivot Tables

Excel for Decision Making

Excel for Finance

Excel Analysis Toolpak

Power BI

Excel

Word

Outlook

Excel Expert

Excel Customer Service

PowerPoint

Forum Posts: 1939

Member Since:
November 8, 2013

Offline

August 21, 2019 - 1:35 pm

Hi Chris,

You can try the attached file.

Chris Yap

Member

Members

Forum Posts: 162

Member Since:
August 21, 2019

Offline

August 21, 2019 - 2:24 pm

Hi Catalin, thanks for your quick response, will take a look and learn it

Chris Yap

Member

Members

Forum Posts: 162

Member Since:
August 21, 2019

Offline

August 21, 2019 - 5:26 pm

Hi Catalin,

In my actual dataset I got 12000 records, however power query can only process the first two columns and in fact I need to transpose all the row values across, how to resolve it ?

Catalin Bombea

Iasi, Romania

Member

Forum Posts: 1939

Member Since:
November 8, 2013

Offline

August 21, 2019 - 6:46 pm

Prepare a more accurate sample file then, to reflect your data structure.

Chris Yap

Member

Members

Forum Posts: 162

Member Since:
August 21, 2019

Offline

August 21, 2019 - 6:57 pm

Hi possible to insert a count for User Group ID as a new column in power query ?

Catalin Bombea

Iasi, Romania

Member

Forum Posts: 1939

Member Since:
November 8, 2013

Offline

August 21, 2019 - 11:55 pm

Sure, Add a column with this formula:

=Table.ColumnCount([Custom])

Chris Yap

Member

Members

Forum Posts: 162

Member Since:
August 21, 2019

Offline

August 22, 2019 - 3:20 pm

Hi Catalin,

for my case already expanded to different columns

how to use power query script to achieve column B (See attached)

Thanks a millions

Catalin Bombea

Iasi, Romania

Member

Forum Posts: 1939

Member Since:
November 8, 2013

Offline

August 22, 2019 - 3:46 pm

You can count before or after expanding the table, does not matter, see attached file.

Answers Post

Chris Yap

Member

Members

Forum Posts: 162

Member Since:
August 21, 2019

Offline

August 22, 2019 - 7:07 pm

Hi Catalin, this morning I use the formula but it returns all 2, now re-do it and it is correct

thank you So much

Jim Chen

Member

Members

Forum Posts: 65

Member Since:
February 20, 2019

Offline

August 23, 2019 - 10:54 am

Catalin,

Do we have to enter this formula

"= Table.AddColumn(#"Grouped Rows", "Custom", each Table.Transpose(Table.SelectColumns([Count],{"User Group ID"})))"

to create a custom (transpose) column? Can we not use formula, and transpose it? Or can we use Pivot columns?

Thanks!

Jim

Jim Chen

Member

Members

Forum Posts: 65

Member Since:
February 20, 2019

Offline

August 23, 2019 - 12:14 pm

Calalin,

Can you see the video for the question I asked above?

https://www.loom.com/share/5d6.....f1d8e14abe

Thanks!

Jim

Catalin Bombea

Iasi, Romania

Member

Forum Posts: 1939

Member Since:
November 8, 2013

Offline

August 23, 2019 - 1:09 pm

Your attempt to Expand columns is not dynamic. If you will have 10 columns for a specific user, the query will not reveal them because the columns you expanded will never change. (There is a Load More link in your expand columns step, if you want to see all columns)

I added the following formula to replace the hard typed list of columns, this is dynamic and will work, no matter if a user will have 5 or 100 columns:

Table.ColumnNames(Table.Combine(#"Added Custom"[Custom])),

This will combine all the tables from the Custom column and extract the list of columns.

Catalin Bombea

Iasi, Romania

Member

Forum Posts: 1939

Member Since:
November 8, 2013

Offline

August 23, 2019 - 1:27 pm

Hi Jim,

Don't be afraid to try, if you think Pivot Column should work. It will not work, because Grouped column has tables, not values.

If you click on a table from Grouped column, you will see that contains tables with a variable row size: first has 3 rows for the same user id, the larges has 5 rows for that id. (that was our intention, to get the full list of Group ID's for a specific user.)

If you don't want to transpose, why don't you try to expand the Grouped column tables? It's always good to try something, anything, it will help you understand why it's a bad idea.

There are many ways to get to the same result, you have to design your way through, analyzing results of the previous step and try to find a solution.

From the #"Grouped Rows" step, you can go for a different route, you can add this step:

= Table.AddColumn(#"Added Custom2", "Custom", each Text.Combine([Grouped][User Group ID],"|")) (add a column that will combine the text from User Group ID from Custom column with "|" as delimiter)

Then just use the Split Column button from interface, with "|" as delimiter.

All depends on your knowledge level, first you should know what all interface commands are doing. You can also use the microsoft knowledge database to read about power query functions, they have a good resource, all functions are grouped into main categories like: List Functions, Table Functions, Text Functions and so on.