Log In
Register
Home
August 21, 2019
Offline
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
November 8, 2013
Offline
Hi Chris,
You can try the attached file.
August 21, 2019
Offline
Hi Catalin, thanks for your quick response, will take a look and learn it
August 21, 2019
Offline
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 ?
November 8, 2013
Offline
Prepare a more accurate sample file then, to reflect your data structure.
August 21, 2019
Offline
Hi possible to insert a count for User Group ID as a new column in power query ?
November 8, 2013
Offline
Sure, Add a column with this formula:
=Table.ColumnCount([Custom])
August 21, 2019
Offline
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
November 8, 2013
Offline
You can count before or after expanding the table, does not matter, see attached file.
Answers Post
August 21, 2019
Offline
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
Power BI
February 20, 2019
Offline
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
Power BI
February 20, 2019
Offline
Calalin,
Can you see the video for the question I asked above?
https://www.loom.com/share/5d6.....f1d8e14abe
Thanks!
Jim
November 8, 2013
Offline
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.
November 8, 2013
Offline
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.
1 Guest(s)
