Power BI - Use List to Generate Column Headers when Splitting Column by Delimiter

[Anonymous]

Hi,

I want to be able to dymanically name Column Headers from a List when splitting a field by a delimiter.

I'm using 2 parameters to split my column by a delimiter - one to give the delimiter, the other to give the number of times to split the column. Here's the Power Query M Code for this step:

= Table.SplitColumn(#"Taxonomy Pattern - Campaign Name", "Taxonomy_Campaign", Splitter.SplitTextByDelimiter(#"DV360 Campaign Name - Delimiter", QuoteStyle.Csv), List.Transform({1..#"DV360 Campaign Name - Count of Delimiters"+1}, each "Campaign Field."&Text.From(_)))

• "Taxonomy_Campaign" is the field I'm splitting
• "DV360 Campaign Name - Delimiter" is the parameters with the delimiter I'm using
• "DV360 Campaign Count of Delimiters"+1 is the number of columns to create
• "Campaign Field."&Text.From(_) generated the current column headers, e.g. "Campaign Field.1", "Campaign Field.2", etc

I have a list query that will have a Header value for each Column, in row order:

I'd like to be able to dynamically update the Column Header so that the first split column says "Campaign Field.IO Number". The second says "Campaign Field.Client Code" and so on.

Does anyone know I can amend the M Code at the Split Column stage so the column headers update dymanically? There will always be the same number of rows in the list as columns being split.

Thanks,

Mark

[amitchandak]

@Anonymous ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Check if these can help

Calculation Groups- Measure Slicer, Measure Header Grouping, Measure to dimension conversion. Complex Table display : https://youtu.be/qMNv67P8Go0

Show dynamic column name
https://perytus.com/2021/02/04/improve-ux-show-year-in-legend-when-using-time-intelligence-measures/

dynamic Column Header
https://www.youtube.com/watch?v=yEemVBiaTuk
https://www.goodly.co.in/dynamic-column-names-power-query/
https://www.thebiccountant.com/2018/07/04/efficiently-rename-column-names-with-a-function-in-power-bi-and-power-query/
https://community.powerbi.com/t5/Desktop/Dynamic-column-name-from-its-value/td-p/189442

[Anonymous]

Hi @amitchandak ,

I posted a whole response to this, but for some reason it's totally disappeared. However ... I've made some progress with a potential solution. But I still need some help.

I've done a standard splittextbydelimiter to see the coding. When I do this, I see the following:

Table.SplitColumn(#"Taxonomy Match - Line Item", "Campaign_Name", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Campaign_Name.1", "Campaign_Name.2", "Campaign_Name.3", "Campaign_Name.4", "Campaign_Name.5", "Campaign_Name.6", "Campaign_Name.7"})

So I wondered if I could perhaps convert my list into a string, to then pass through where different Column Names appear. So I tried the following:

= Table.SplitColumn(#"Taxonomy Match - Line Item", "Campaign_Name", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {Text.Combine(#"DV360 Campaign Name - Fields",",")})

With this I'm trying to create the string. In my list table, I added "" to the start and end of each row. However when I go this, it renames the first column with this string, as below:

Do you know how I can convert this string to replace the code so it uses each field from the list as a Header for each column?

Thanks,

Mark

[Anonymous]

Hi @amitchandak ,

I'm not able to upload a PBIX or Excel file so I'll need to copy some rows into a table:

Campaign ID	Campaign Name	Taxonomy_Campaign	MinDate	MaxDate	Impressions
1	036029 | NNOOPH | FYNSOH | SYDNEY OPERA HOUSE | FIND YOUR SYDNEY OPERA HOUSE | PO CODE (MBA) | 04-DEC-22 | 18-DEC-22	036029 | NNOOPH | FYNSOH | SYDNEY OPERA HOUSE | FIND YOUR SYDNEY OPERA HOUSE | PO CODE (MBA) | 04-DEC-22 | 18-DEC-22	08/12/2022	18/12/2022	338831
2	035021 | NTRTRN | EDDYAA | TRANSPORT FOR NSW | EDDY AVENUE | PO CODE (MBA) | 14-NOV-22 | 25-JAN-23	035021 | NTRTRN | EDDYAA | TRANSPORT FOR NSW | EDDY AVENUE | PO CODE (MBA) | 14-NOV-22 | 25-JAN-23	16/11/2022	18/12/2022	1065397
3	033925 | NGDOCS | N/A | NSW GOV - SYDNEY OPERA HOUSE | AMADEUS | N/A | 06-OCT-22 | 22-JAN-23	033925 | NGDOCS | N/A | NSW GOV - SYDNEY OPERA HOUSE | AMADEUS | N/A | 06-OCT-22 | 22-JAN-23	08/10/2022	18/12/2022	743704
4	033911 | NLREPA | CLEANA | NSW GOV - ENVIRONMENTAL PROTECTION AUTHORITY | CLEAN FILL | 07-OCT-22 | 31-OCT-23	033911 | NLREPA | CLEANA | NSW GOV - ENVIRONMENTAL PROTECTION AUTHORITY | CLEAN FILL | 07-OCT-22 | 31-OCT-23	26/10/2022	01/11/2022	398403
5	034744 | NTRTRN | DRKDRA | NSW GOV - TRANSPORT NSW | DRINK DRIVING | PO CODE (MBA) | 26-OCT-22 | 25-MAY-23	034744 | NTRTRN | DRKDRA | NSW GOV - TRANSPORT NSW | DRINK DRIVING | PO CODE (MBA) | 26-OCT-22 | 25-MAY-23	26/10/2022	27/10/2022	9
6	033710 | NLRTAR | WLDREA | TARONGA ZOO | WILDLIFE RETREAT | VIDEO | 06-OCT-22 | 17-DEC-22	033710 | NLRTAR | WLDREA | TARONGA ZOO | WILDLIFE RETREAT | VIDEO | 06-OCT-22 | 17-DEC-22	06/10/2022	18/12/2022	2809713
7	035824 | NNROPH | VELVRA | NSW GOV - SYDNEY OPERA HOUSE | VELVET REWIRED | PO CODE (MBA) | 21-NOV-22 | 17-DEC-22	035824 | NNROPH | VELVRA | NSW GOV - SYDNEY OPERA HOUSE | VELVET REWIRED | PO CODE (MBA) | 21-NOV-22 | 17-DEC-22	29/11/2022	18/12/2022	165450
8	035181 | NLREPA | ASBAWA | NSW GOV - ENVIRONMENTAL PROTECTION AUTHORITY | ASBESTOS AWARENESS | 13-NOV-22 | 30-JUN-23	035181 | NLREPA | ASBAWA | NSW GOV - ENVIRONMENTAL PROTECTION AUTHORITY | ASBESTOS AWARENESS | 13-NOV-22 | 30-JUN-23	18/11/2022	18/12/2022	1327217
9	034744 | NTRTRN | DRKDRA | NSW GOV - TRANSPORT FOR NSW | DRINK DRIVING | PO CODE | YOUTUBE | 06-OCT-22 | 30-OCT-22	034744 | NTRTRN | DRKDRA | NSW GOV - TRANSPORT FOR NSW | DRINK DRIVING | PO CODE | YOUTUBE | 06-OCT-22 | 30-OCT-22	08/10/2022	31/10/2022	2336278

I'm splitting the Taxonomy_Campaign column. It's the result of this splilt that I'm trying to dynamically rename.

I've given the M Query info for this too, if that helps:

Main Table - 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("xVbbbuMgEP0VlKddqVZgMLdHGkji1oEIY0feKn/Sj1+InbXTOtLuSk1frMEY5szMOTN+e1sxUMAxw6unFaYcg0LvyDnvj/tkbHvX+Gw0vXG2R/5og0Z73zY271bOoN63YXn76NHGG4t+HJ71z7TGZWHspgBINpGj/Y1u5ZrAGvBlQeYLSqWkZHV+ytmRnGCiLjAZBpJhxhCDS4Y1ptc6GTFo1xx9iGjrA3LNadxEurOuXQJFysL5bgAFrHjRrgD6KCeErwlZjJxgzqgSY+gCK6DygooqYBnVzvhNk411hpRB7HyHiuVK6IM2tp0+x7zwmzjigXnQX3t9ihAvhitKKnB5jbZkDMgIh1xqUAd7zDdvaqvdLSLruip4d7Au6hodg492EyvvkG7j3ocq9tdzia91neGJCR4lg00f7w74LBuYzJhAlSwxvWaDUKBDNkpRlnNGmvBqwi28iZsDL02o3Gt+dpXbLTAT5qVixUH311w80NlNJkDMFuqaBMCMsKFGguChRlGHZJxqE+xFljrVZafRL+/z66o2dbW1KNiY9mN61VXG+g/0JGLe/r7wbn6P+iCxSjW+NjnFFQwjgEm4FMCFoRd3tu7CX6gxf2djwnaqgjVLZSBTN7qN/+EuQd1tgJyVaRaOWSk5yBEikTcS1c2zPv2HRNM520TfoHQ6WGeb3L4InWBSXLy00zD4Drc5IXeyQ5MkiJgaZvpz+HfVTuPrnnLf83iP7bP9QO2EcrC/zel8lFAy7x+UchDyT/cErvgobpBzcbs6qfmzuPNzGyrrTJOG1xroZxQzXnzFpeJeq1BckDwZz+ff", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Campaign_ID = _t, Campaign_Name = _t, Taxonomy_Campaign = _t, MinDate = _t, MaxDate = _t, Total_Impressions = _t]),
    #"Split Column by Delimiter - Campaign Name" = Table.SplitColumn(Source, "Taxonomy_Campaign", Splitter.SplitTextByDelimiter(#"DV360 Campaign Name - Delimiter", QuoteStyle.Csv), List.Transform({1..#"DV360 Campaign Name - Count of Delimiters"+1}, each "Campaign Field."&Text.From(_)))
in
    #"Split Column by Delimiter - Campaign Name"

Here are the look up fields, in row order:

1	IO Number
2	Client Code
3	Product Code
4	Campaign
5	Detail
6	Start Date
7	End Date

And here's the M Query for this table:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8vRX8CvNTUotUorViVZyzslMzStRcM5PSQXzA4ryU0qTkQScE3MLEjPT88Acl9SSxMwcMDO4JLGoRMElsQSizDUvBcqJBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    Column1 = Source[Column1]
in
    Column1

Thanks,

Mark

[Anonymous · Accepted solution]

Hi @amitchandak ,

I figured out how to do this.

Rather than having the List.Transform function select the number of columns, I added referenced my list here, then amended the "each" clause to it pulled the name from the list. As below:

= Table.SplitColumn(#"Added Data Source", "Taxonomy_Campaign", Splitter.SplitTextByDelimiter(#"DV360 Campaign Name - Delimiter", QuoteStyle.Csv), List.Transform(#"DV360 Campaign Name - Fields", each Text.From(_)))

Thanks,

Mark