Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Description | Symbol | CUSIP |
Invesco Qqq Tr | ||
Unit Ser 1 | QQQ | 46090E103 |
Select Sector Spdr Trust | ||
The Technology Select Sector | ||
Spdr Fund | XLK | 81369Y803 |
Spdr S&P 500 Etf Trust | SPY | 78462F103 |
(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:
Description | Symbol | CUSIP |
Invesco Qqq Tr Unit Ser 1 | QQQ | 46090E103 |
Select Sector Spdr Trust The Technology Select Sector Spdr Fund | XLK | 81369Y803 |
Spdr S&P 500 Etf Trust | SPY | 78462F103 |
Thanks in advance for your help / suggestions.
Solved! Go to Solution.
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
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
That worked perfectly. Thank you very much!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.