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

Hi amitchandak ,

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

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:

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

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