Split Column by Delimiter and add new column Name Dynamically

Q

QMAN223

New Member
Joined
Nov 24, 2021
Messages
36
Office Version
  1. 365
Platform
  1. Windows
Hello Excel Gurus!

I have a challenge which I cant' quite tackle using Power Query but I'm sure there's a genius out there who can!

My problem is as follows:

1. I have a "Colors" column which contains a list of concatenated colors
2. I want to split them by column and then dynamically rename each of the columns with the header name iterated by the next value (see example in Table 2)

Table 1 - Data from SharePoint Multiselect Field
Colors
Red,Blue,Green
Purple
Grey,Brown,Grey,Hot Pink
White,Amber
Yellow,Orange,Black,Greem,Turqoise

Table 2 - How I want to see the data in Excel Spreadsheet
ColorsHeadername 1Headername 2Headername 3Headername 4Headername 5Headername 6 and so on...
Red,Blue,GreenRedBlueGreen
Purple,Turqoise,BluePurple
Grey,Brown,Grey,Hot PinkGreyBrownGreyHot Pink
White,AmberWhiteAmber
Yellow,Orange,Black,Greem,TurqoiseYellowOrangeBlackGreenTurqoise

I figured out the easy part of splitting column by delimiter but I don't know how to add a dynamic column header name

Many thanks for your time and help with this one!
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tbl = Table.DuplicateColumn(Source, "Colors", "Dup"),
    MaxCols = List.Max(List.Transform(tbl[Colors], each Text.Length(_) - Text.Length(Text.Remove(_,",")) + 1)),
    ColList = List.Accumulate({1..MaxCols}, {}, (s,c)=> s & {"Headername " & Text.From(c)}),
    Result = Table.SplitColumn(tbl, "Dup", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), ColList)
in
    Result

Book1
ABCDEFGHI
1Table1Query Output
2ColorsColorsHeadername 1Headername 2Headername 3Headername 4Headername 5
3Red,Blue,GreenRed,Blue,GreenRedBlueGreen
4PurplePurplePurple
5Grey,Brown,Grey,Hot PinkGrey,Brown,Grey,Hot PinkGreyBrownGreyHot Pink
6White,AmberWhite,AmberWhiteAmber
7Yellow,Orange,Black,Greem,TurqoiseYellow,Orange,Black,Greem,TurqoiseYellowOrangeBlackGreemTurqoise
8
Sheet1
 
Upvote 1
Solution
You must log in or register to reply here.

Similar threads

V
  • Question
Power Query Split Column Dynamically
Replies
8
Views
650
GraH
GraH
M
  • Solved
If List 1 match with any in List 2 then true
Replies
1
Views
322
JGordon11
JGordon11
L
  • Solved
Add original hour column to accompany date column without datetime.
Replies
2
Views
176
LambChoptheKid
L
M
  • Solved
Is there another way that has less steps
Replies
2
Views
359
JGordon11
JGordon11
J
  • Solved
Power Query: Selection of columns with date headers
Replies
9
Views
410
GraH
GraH

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top