+ Reply to Thread
Results 1 to 6 of 6

Power Query - Concatenate multiple variable entries

  1. #1
    Registered User
    Join Date
    07-27-2020
    Location
    Manchester, UK
    MS-Off Ver
    Office 365
    Posts
    21

    Power Query - Concatenate multiple variable entries

    Hi all, hoping someone may be able to point me in the right direction with this one.

    I'm running a dataset of approx. 40k individuals, and tracking progress of up to 100 unique activities, combining all entries into a single concatenated output for each individual. Currently this works through formulas across multiple sheets (thousands of IF/XLOOKUP statements and the longest concatenate formula in existence) - it works but the file size is massive, and most importantly it doesn't play well when the number of individuals/activities increases.

    Ideally I'm looking to run this entire process through Power Query, whereby changes in individual and activity numbers is not a limiting factor. The source data is itself generated by a different Power Query, so it's nice and easy to reference.

    I'm aiming for the output cell for each individual to detail the full summary of activities conducted (including the date) on a unique line, with activities not completed by that individual not included at all. Individuals completing no activities simply return a blank field.

    Importantly, the name/volume/order of activities and individuals will change constantly; I've made some headway, but I'm struggling to account for these changes.

    Any help would be really appreciated!
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2505 (Windows 11 Home 24H2 64-bit)
    Posts
    91,297

    Re: Power Query - Concatenate multiple variable entries

    See what you think of this M Code:

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    NB:
    as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    07-27-2020
    Location
    Manchester, UK
    MS-Off Ver
    Office 365
    Posts
    21

    Re: Power Query - Concatenate multiple variable entries

    Thank you so much for the help with this - works like a charm.

    I've only been using PQ for a few months, but I'm becoming a total convert!! Thanks again!

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2505 (Windows 11 Home 24H2 64-bit)
    Posts
    91,297

    Re: Power Query - Concatenate multiple variable entries

    You're welcome. I often refer to it as Pandora's Box.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2505 (Windows 11 Home 24H2 64-bit)
    Posts
    91,297

    Re: Power Query - Concatenate multiple variable entries

    Here's a slightly shorter version:

    Please Login or Register  to view this content.

  6. #6
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,222

    Re: Power Query - Concatenate multiple variable entries

    Another one

    Please Login or Register  to view this content.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Power Query - Extract specific data from text and concatenate with delimiter
    By Riahon in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-03-2020, 11:58 AM
  2. [SOLVED] Power Query - excel formula translation into Power Query
    By afgi in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 02-19-2020, 03:38 AM
  3. Power Query - Date List with changing variable
    By Steveapa in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-17-2020, 04:54 PM
  4. Power Query - Date List with changing variable
    By Steveapa in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-17-2020, 12:27 PM
  5. Replies: 4
    Last Post: 02-17-2020, 06:03 AM
  6. How to concatenate cell reference and text in Power Query?
    By basispoints in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-06-2019, 11:22 AM
  7. Sub-Forum for Excel Power Tools (Power Query, Power Pivot & Power BI)
    By chullan88 in forum Suggestions for Improvement
    Replies: 10
    Last Post: 06-28-2018, 02:25 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1