Merge Dynamic Number of Columns

Hello,

I’ve got some raw data that exports into an excel file with some funky formatting.
The data requires merging of different sets of columns but the number of columns in each set can change every week.

Problem:
Data contains free-text entries that can contain html tables. Excel spreads the columns of the html tables into adjacent cells within the dataset. Thus when loading into Power Query, the additional cells are given their own columns but with no header.

The number of columns in an html table is dynamic .
There are multiple free-text variables in the dataset that can contain html tables.

Goal:
Use power query editor to merge these columns into one but be able to handle any number of columns dynamically.

Example of how it looks like in Excel:

Example how it looks like Power Query:

Here is a pbi file that contains my work thus far and what the data should ultimately look like.
The column merging steps related to this post however are in the query labeled ‘Mock Data’.
Mock RAW QMS for COE.xlsx (9.4 KB) RAW QMS Mock for COE.pbix (41.7 KB)

Best regards,
Aaron

Hi @Aaron.Im,

Welcome to the Forum!

If created this supporting query to dynamically create a GroupColumn.

image
.

Created a list that contains all Column names for each Description group

image

.
All put together.

.
Just set the FileLocation parameter and all queries will be restored.
Here’s your file. RAW QMS Mock for COE.pbix (46.5 KB)

I hope this is helpful.

4 Likes

Thank you Melissa! This was brilliant and does exactly what I needed!

Best regards,
Aaron

1 Like