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
Super Userdommyw277 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
- Add an Index column. Use Add Column → Index Column. This keeps each tag linked to its original row after you split it apart.
- 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.
- Remove the leftover quotes. Use Transform → Format → Trim, then Replace Values to replace " with nothing.
- 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.
- 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.
- 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:
| RowID | Portfolio | Created Date | Product |
|---|---|---|---|
| 1 | Generation 1 | 01/01/2021 | Test1 |
| 2 | Generation 1 | 01/01/2021 | Test1 |
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
dommyw277Hi,
I tried this and it was all fine till last step , its taken over an hour and crashed my machine. Its 12 months worth of data so should i try it with only one month, would that still work?
- BeaBF
Yes, testing on one month would work, since the logic doesn't depend on how much data there is. But a smaller file won't fix the slowdown, and the full 12 months would likely hang again. The pivot step is what's slow, so it's better to skip the pivot altogether.
A faster approach: no split into rows, no pivot
This version reads each row's tags into a set of name/value pairs (a "record"), then expands the record into columns. Each row is handled once, on its own, so it stays fast with large files.
let Source = YourPreviousStep, // table with a column named "Tags"
Parsed = Table.AddColumn(Source, "TagRecord", each
let raw = if [Tags] = null then {} else Text.Split([Tags], ""","""),
tags = List.Transform(raw, each Text.Trim(Text.Trim(_), """")), pairs = List.Select( List.Transform(tags, each Splitter.SplitTextByEachDelimiter({":"})(_)), each List.Count(_) = 2),
kv = Table.FromRows( List.Transform(pairs, each {Text.Trim(_{0}), Text.Trim(_{1})}), {"Name", "Value"}),
dedup = Table.Distinct(kv, {"Name"}) // keeps the first value if a tag repeats in Record.FromTable(dedup), type record),
Expanded = Table.ExpandRecordColumn(Parsed, "TagRecord", {"Portfolio", "Created Date", "Product"}) // list the tag names you need in Expanded
Let me know and please accept the answer as solution.BBF
- dommyw277
So i do everything up until the pivot then enter that code?