• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

how to transpose a particular column values that tie to userid into Cross table (pivot) in Power Query|Power Query|Excel Forum|My Online Training Hub

You are here: Home / how to transpose a particular column values that tie to userid into Cross table (pivot) in Power Query|Power Query|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower Queryhow to transpose a particular colum…
sp_PrintTopic sp_TopicIcon
how to transpose a particular column values that tie to userid into Cross table (pivot) in Power Query
Avatar
Chris Yap
Member
Members
Level 0
Forum Posts: 162
Member Since:
August 21, 2019
sp_UserOfflineSmall Offline
1
August 21, 2019 - 1:19 pm
sp_Permalink sp_Print

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.

sp_AnswersTopicSeeAnswer See Answer
Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1810
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
2
August 21, 2019 - 1:35 pm
sp_Permalink sp_Print

Hi Chris,

You can try the attached file.

Avatar
Chris Yap
Member
Members
Level 0
Forum Posts: 162
Member Since:
August 21, 2019
sp_UserOfflineSmall Offline
3
August 21, 2019 - 2:24 pm
sp_Permalink sp_Print

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

Avatar
Chris Yap
Member
Members
Level 0
Forum Posts: 162
Member Since:
August 21, 2019
sp_UserOfflineSmall Offline
4
August 21, 2019 - 5:26 pm
sp_Permalink sp_Print

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 ?

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1810
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
5
August 21, 2019 - 6:46 pm
sp_Permalink sp_Print

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

Avatar
Chris Yap
Member
Members
Level 0
Forum Posts: 162
Member Since:
August 21, 2019
sp_UserOfflineSmall Offline
6
August 21, 2019 - 6:57 pm
sp_Permalink sp_Print

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

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1810
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
7
August 21, 2019 - 11:55 pm
sp_Permalink sp_Print

Sure, Add a column with this formula:

=Table.ColumnCount([Custom])

Avatar
Chris Yap
Member
Members
Level 0
Forum Posts: 162
Member Since:
August 21, 2019
sp_UserOfflineSmall Offline
8
August 22, 2019 - 3:20 pm
sp_Permalink sp_Print

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

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1810
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
9
August 22, 2019 - 3:46 pm
sp_Permalink sp_Print

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

sp_AnswersTopicAnswer
Answers Post
Avatar
Chris Yap
Member
Members
Level 0
Forum Posts: 162
Member Since:
August 21, 2019
sp_UserOfflineSmall Offline
10
August 22, 2019 - 7:07 pm
sp_Permalink sp_Print

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

Avatar
Jim Chen
Member
Members

Power BI
Level 0
Forum Posts: 54
Member Since:
February 20, 2019
sp_UserOfflineSmall Offline
11
August 23, 2019 - 10:54 am
sp_Permalink sp_Print

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

Avatar
Jim Chen
Member
Members

Power BI
Level 0
Forum Posts: 54
Member Since:
February 20, 2019
sp_UserOfflineSmall Offline
12
August 23, 2019 - 12:14 pm
sp_Permalink sp_Print

Calalin,

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

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

 

Thanks!

 

Jim

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1810
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
13
August 23, 2019 - 1:09 pm
sp_Permalink sp_Print

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.

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1810
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
14
August 23, 2019 - 1:27 pm
sp_Permalink sp_Print

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.

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Kim Knox, Nada Perovic, techno tux, Othman AL MUTAIRI
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 871
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
Jessica Stewart: 205
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Othman AL MUTAIRI
Misael Gutierrez Sr.
Attif Ihsan
Kieran Fee
Murat Hasanoglu
Brett Dryland
Saeed Aldousari
Bhuwan Devkota
Kathryn Patton
Maria Conatser
Forum Stats:
Groups: 3
Forums: 24
Topics: 6222
Posts: 27293

 

Member Stats:
Guest Posters: 49
Members: 31916
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.