Split & Merge Columns – Pitfalls #7 and #8

In today’s post, we will introduce the 7th and 8th pitfalls in Power BI and Power Query in Excel. To follow the pitfalls series, go here.

The 7th and 8th pitfalls were already covered on DataChant as separate articles. So let’s briefly present them, and allow you to jump straight into the original posts, and save me some time to start the groundwork for the final two pitfalls (9th and 10th), which are going to be extremely helpful.

Pitfall #7 – Split Columns

The 7th pitfall is more dangerous than most of the refresh-failing pitfalls. It will not lead to a refresh failure, but to incorrect reports. Like the filtering pitfall (here), with the default behavior of column splitting, you could miss data.

Let’s demonstrate the 7th pitfall – Below we have a table of events, with comma-separated values in the last column for the participants in each event.

When we split Participants by delimiter, we get the following screen.

Tempted to click OK above, you may miss the fact that Power Query decided to split the participants to 7 columns (and never more than 7).

Fortunately, we’ve become savvy users, and frequently check the formula bar (Do you remember the first pitfall?), we know that the auto-generated formula, will ignore participants , for rows with more than 7 participants.

So, why did Power Query generated a code that relies on 7 columns? That was the maximal number of comma-separated values in the preview.

To learn about the solution in details, continue reading here.

Fortunately, we can manipulate the following formula:

= Table.SplitColumn(Source,"Participants",Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Participants.1", "Participants.2", "Participants.3", "Participants.4", "Participants.5", "Participants.6", "Participants.7"})

By replacing the second argument (the long list of columns), with the number of columns to split into:

= Table.SplitColumn(Source,"Participants",Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), 10)

But there is a better solution if you plan to unpivot the columns. Continue reading the original article here.

Pitfall #8 – Merge Columns

Often times, we need to perform the opposite operation of split, and merge multiple columns into a single column. Like in reordering, renaming and removing of columns, the auto-generated formula is prone to refresh errors.

To create a robust query that merges multiple columns, continue reading here, and learn about dynamic change of types, and the application of merge by column positions.

Coming next: Pitfall #9 –  Expanding table columns.