+ Reply to Thread
Results 1 to 6 of 6

Concat spilled data

  1. #1
    Registered User
    Join Date
    04-20-2020
    Location
    London
    MS-Off Ver
    2016
    Posts
    4

    Concat spilled data

    Hola

    I am trying to concat some cells but there is not a pattern so I am having difficulties to find a easy way to do it. We are getting an extract from our website that include elements and attributes attached to a category. Each category is a column. If a element has various attributes for a category, the export is spelling the attributes in different rows, generating blank spaces between elements. The desire output is all the attributes for a category in the same cell delimited by comma.

    Is there any macro or PQ to do this easily? I have attached a sample with demo data. Please, let me know if you need more info as I am not sure if I have explained really clear :P

    TIA
    Attached Files Attached Files

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

    Re: Concat spilled data

    Open Power Query/Get and Transform. Click on New Query.

    Open blank query in the editor, launch Advanced Editor and paste in the following code.

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

  3. #3
    Registered User
    Join Date
    04-20-2020
    Location
    London
    MS-Off Ver
    2016
    Posts
    4

    Re: Concat spilled data

    Hi Bo_Ry

    Thank you for the advise, that put me closer to the goal but still I need some changes. With the PQ you have advise, if there is only one attribute in the column for that element, it is duplicating it. For example, front door in the file you have uploaded. Is there any way to avoid that?

    Thanks again

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

    Re: Concat spilled data

    Add List.Distinct

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filled Down" = Table.FillDown(Source,{"Key", "Shape", "Colour","Open"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"Key", "Shape"}, {{"Color", each Text.Combine(List.Distinct([Colour]),", ")}, {"Open", each Text.Combine(List.Distinct([Open]),", ")}})
    in
    #"Grouped Rows"
    Attached Files Attached Files
    Last edited by Bo_Ry; 04-20-2020 at 09:59 AM.

  5. #5
    Registered User
    Join Date
    04-20-2020
    Location
    London
    MS-Off Ver
    2016
    Posts
    4

    Re: Concat spilled data

    That is perfect! you saved me a lot of time and headaches! Also, gave me an idea for another project I am working on so 2 in 1.

  6. #6
    Registered User
    Join Date
    04-20-2020
    Location
    London
    MS-Off Ver
    2016
    Posts
    4

    Re: Concat spilled data

    Sorry, one more question.

    Depending what I am exporting, I may not have all the columns. IE, in my real export file, I have 16 classes but not all the export files will have the 16 columns. If they catalogue I am exporting doesn't have attributes on one of the classes, the file will not contain that column. Is this a way to fix this in the query? At the moment, it is giving me an error and I have to add the columns manually with empty data.

    It is not the end of the world but as will be various people using this template I would like to make it as simple as possible.

    Thanks again

+ 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. Countifs / Sumifs with dynamic arrays (spilled ranges)
    By esbencito in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 03-08-2020, 02:43 PM
  2. Dynamic, Spilled, Named Ranges - how to override SPILL
    By TimB in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-24-2020, 01:53 PM
  3. [SOLVED] YTD with dynamic spilled array
    By MatthewHart74 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 12-14-2019, 07:49 AM
  4. YTD with spilled dynamic array
    By MatthewHart74 in forum Office 365
    Replies: 0
    Last Post: 12-12-2019, 12:05 PM
  5. [SOLVED] Use CONCAT to pull data from different places
    By burrjc in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-29-2019, 03:40 PM
  6. Replies: 4
    Last Post: 04-09-2019, 09:28 PM
  7. Concat column data in single cell with Matching ID
    By abhit_kumar in forum Excel General
    Replies: 7
    Last Post: 10-04-2012, 09:01 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