Hi Chris,

You can try the attached file.

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

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 ?

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

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

Sure, Add a column with this formula:

=Table.ColumnCount([Custom])

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

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

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

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

Calalin,

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

https://www.loom.com/share/5d60f7f51c8844a782d7caf1d8e14abe

Thanks!

Jim

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.

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.