86 - Split columns, varying number of columns (Power Query)
Suppose you have a column with multiple values stored in it, with delimiters separating the values e.g. "a,b,c".
You want to split these into new columns.
However, in the future you may need to add more or fewer new columns. You don't know yet.
The first solution shows how to use List.Max to get the number of new columns to add.
Then I show how to use List Generate to create the new headers, instead of new headers being called "Split.1" and then you need List Accumulate to fix the headers.
Lastly I convert the query into a proper function.
This proves to be difficult because my function acts on a table meaning I can't link a query to the function.
Secondly, I need a variable to know what column to act on, but Power Query doesn't know that "[ColumnName]" means "interpret this variable as a column". I need other functions, like Table Column, to convert text to a column header.
Yes, I could redo the video without the mistakes. It's beneficial to see mistakes happen, see how they're understood and how they're solved.
00:00 Premise
01:23 Count max delimiters
04:04 List Accumulate to fix headers
06:35 List Generate for new headers
08:44 As a function
09:23 Adding variables
10:15 Debug part 1
10:40 Temporarily add variables as new steps
11:19 Using a variable for a column header
12:12 Debug part 2
12:51 Table Column
14:31 I forgot a "." in the name
15:25 Optional new column name as a variable
Watch video 86 - Split columns, varying number of columns (Power Query) online without registration, duration hours minute second in high quality. This video was added by user WesleySon 14 August 2024, don't forget to share it with your friends and acquaintances, it has been viewed on our site 116 once and liked it 2 people.