Forum Discussion

pqsmr's avatar
pqsmr
Regular Visitor
3 years ago
Solved

Combine text split across multiple rows

I have a table with a Description column with text that has been divided into sections and split across one or more rows.

 

I would like to combine the related rows together into a single row using Power Query in Excel.

 

A example input table might be the following:

 

DescriptionSymbolCUSIP
Invesco Qqq Tr  
Unit Ser 1QQQ46090E103
Select Sector Spdr Trust  
The Technology Select Sector  
Spdr FundXLK81369Y803
Spdr S&P 500 Etf TrustSPY78462F103

 

(Note that there are more columns than I've shown, so I would like a solution that works no matter how many columns the table contains.)

 

In the above example, the description for Symbol QQQ spans two rows, the Description for Symbol XLK spans three rows, and the description for Symbol SPY spans only one row.

 

I would like to transform this source table using Power Query into this output table:

 

DescriptionSymbolCUSIP
Invesco Qqq Tr Unit Ser 1QQQ46090E103
Select Sector Spdr Trust The Technology Select Sector Spdr FundXLK81369Y803
Spdr S&P 500 Etf TrustSPY78462F103

 

Thanks in advance for your help / suggestions.

Need Help
  • HotChilli's avatar
    HotChilli
    3 years ago

    If there is consistency with the way rows with Symbol and CUSIP are related to the rows with blank values above them:

    You can do this (mostly with the interface)-

    Replace blank or space values with null.

    Do a Fill Up on the 2 columns.

    Do a 'Group By' on Symbol and CUSIP with one aggregation (pick Min, we will edit this in the next step)

    Edit the last step in the formula bar to replace 'List.Min' with 'Text.Combine'.

    -- 

    It will be a little bit more complicated with other columns but that's the start

     

2 Replies

  • HotChilli's avatar
    HotChilli
    Community Champion
    3 years ago

    If there is consistency with the way rows with Symbol and CUSIP are related to the rows with blank values above them:

    You can do this (mostly with the interface)-

    Replace blank or space values with null.

    Do a Fill Up on the 2 columns.

    Do a 'Group By' on Symbol and CUSIP with one aggregation (pick Min, we will edit this in the next step)

    Edit the last step in the formula bar to replace 'List.Min' with 'Text.Combine'.

    -- 

    It will be a little bit more complicated with other columns but that's the start

     

    • pqsmr's avatar
      pqsmr
      Regular Visitor
      3 years ago

      That worked perfectly.  Thank you very much!