Splitting Tag data

dommyw277​ Hi!

Yes, you can. Power Query can match each tag by its name, so the order doesn't matter. Split each string into one row per tag, split each tag into a name and a value, then pivot the names into columns.

Steps in Power Query Editor

1. Add an Index column. Use Add Column → Index Column. This keeps each tag linked to its original row after you split it apart.
2. Split the tags into rows. Select the tag column, then Split Column → By Delimiter. Use "," as a custom delimiter (quote, comma, quote) and pick Advanced options → Split into Rows.
3. Remove the leftover quotes. Use Transform → Format → Trim, then Replace Values to replace " with nothing.
4. Split each tag into name and value. Use Split Column → By Delimiter, set the delimiter to : and choose Left-most delimiter. This splits only at the first colon, so a value with a time in it (such as 10:30) stays in one piece. Rename the two new columns TagName and TagValue, then trim both.
5. Pivot the names into columns. Select TagName, then Transform → Pivot Column. Set Values Column to TagValue and Advanced → Aggregate Value Function to Don't Aggregate.
6. Clean up. Remove the Index column if you don't need it, and set data types. For example, make Created Date a date using Using Locale if the date format is dd/MM.

The same thing in M (Advanced Editor)

let Source = YourPreviousStep, // table with a column named "Tags" 
AddIndex = Table.AddIndexColumn(Source, "RowID", 1, 1, Int64.Type),
ToList = Table.AddColumn(AddIndex, "Tag", each Text.Split([Tags], ""","""), type list),
RemoveOrig = Table.RemoveColumns(ToList, {"Tags"}),
ToRows = Table.ExpandListColumn(RemoveOrig, "Tag"),
CleanQuote = Table.TransformColumns(ToRows, {{"Tag", each Text.Trim(Text.Trim(_), """"), type text}}),
SplitKV = Table.SplitColumn(CleanQuote, "Tag", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.None, false), {"TagName", "TagValue"}),
TrimKV = Table.TransformColumns(SplitKV, {{"TagName", Text.Trim, type text}, {"TagValue", Text.Trim, type text}}),
Pivoted = Table.Pivot(TrimKV, List.Distinct(TrimKV[TagName]), "TagName", "TagValue", each List.First(_)) // safe if a tag name appears twice in one row in Pivoted

Both of your example strings would end up in the same columns:

Things to know:

• Missing tags: if a row doesn't have a tag, that cell is blank (null).
• New tag names: new names in future refreshes become new columns automatically, because the pivot reads the list of tag names from the data each time.
• Repeated tags: with Don't Aggregate, a row that repeats the same tag name causes an error. The M version above avoids that by keeping the first value (List.First).

Let me know. BBF

Hi dommyw277​,

Yes. Since the tags come in any order, split them by name rather than by position: turn each tag into a name/value record and let Power Query build the columns from the names. A custom column does it in one step:

// Add Column > Custom Column, on your text column [Tags]
= Record.FromList(
      List.Transform(Text.Split([Tags], ","), each Text.Trim(Text.AfterDelimiter(_, ":"))),
      List.Transform(Text.Split([Tags], ","), each Text.Trim(Text.BeforeDelimiter(_, ":")))
  )

// then click the expand icon on the new column and tick the tag names you want
// (Portfolio, Created Date, Product, ...)

Text.BeforeDelimiter / Text.AfterDelimiter split only on the first colon, so values like times (12:30) stay intact, and tags missing from a row simply come out as null after expanding.

Two small things to watch: if a tag name can appear twice in one string, Record.FromList will error, so add List.Distinct or keep the first occurrence; and after expanding, set the data types (for example Created Date as date with the right locale for dd/mm/yyyy).

So my overall approach would be:

Split each string on "," into tags
-> Each tag into name (before first ":") and value (after it)
-> Build a record per row, expand by tag name
-> Set data types per new column

AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.

One small thing if you use the custom-column version: your tags are wrapped in double quotes, so splitting on "," leaves them attached and the columns come out as "Portfolio with values like Generation 1". Trimming the quotes inside the transform fixes it:

List.Transform(Text.Split([Tags], ","), each Text.Trim(Text.Trim(_), """"))

BeaBF's step-by-step version already handles this with its Replace Values step.