Forum Discussion
Helper VSplitting Tag data
dommyw277 Try with:
let
Source = AzureCostManagement.Tables("Enrollment Number", "REDACTED", 12, [startDate=null, endDate=null]),
usagedetails = Source{[Key="usagedetails"]}[Data],
// Read the Tags text as JSON and turn it into a record (name/value pairs)
ParsedTags = Table.AddColumn(usagedetails, "TagRecord", each
let
t = if [Tags] = null then "" else Text.Trim([Tags]),
j = if Text.StartsWith(t, "{") then t else "{" & t & "}",
r = try Json.Document(j) otherwise null
in
if r is record then r else null,
type nullable record),
// One column per tag. List the tag names you need, spelled exactly as in the data
ExpandedTags = Table.ExpandRecordColumn(ParsedTags, "TagRecord",
{"Creator", "Portfolio", "Created Date", "Product"})
in
ExpandedTags
Memorable MemberHi 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 columnAI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.