Forum Discussion
Column transformation
Hello
Thanks for your answer. I found a solution today:
let
Source = AzureCostManagement.Tables("Enrollment Number", "xxxxx", 8, []),
usagedetails = Source{[Key="usagedetails"]}[Data],
#"Added Custom" = Table.AddColumn(usagedetails, "Tags JSON", each Text.Combine({"{ ", [Tags], " }"})),
#"Parsed JSON" = Table.TransformColumns(#"Added Custom",{{"Tags JSON", Json.Document}}),
#"Tags JSON développé" = Table.ExpandRecordColumn(#"Parsed JSON", "Tags JSON", {"CMDB", "Contact", "Environment"}, {"Tags JSON.CMDB", "Tags JSON.Contact", "Tags JSON.Environment"})
in
#"Tags JSON développé"
Sorry for the few informations I gave. Below a link to a picture.
My problem is to split TAGS
- example: "stage":"all","Scope":"All",....
But the order is randomized.
I would like to split TAGS values into columns.
Already discuss here but without good result...
https://community.powerbi.com/t5/Desktop/Azure-Cost-Management-Splitting-Out-Tags/td-p/948823
I hope I give you right information now.
Thanks again for your help.
Hi cgeorgeot,
I knew you'd have different tags, that's why I've been mentioning the extra step of getting the columns from all the record fields. This is what's probably taking a lot of time. If you know the full list of tags and they're not that many then you could hardcode them and win a lot of computing time by just removing completely empty columns in the end.
The code I've provided should work for you, if not, please show us your code and where this list error appears, because in my environment it runs as it should:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZBBDoIwEEWvMpm1J3DjVk/ggrIYYZSGdoaUQuPthUIUQ6Jp0sXP++83LQrUJByOBiuug60MHgw6rShaFYNTfg8kFeecZcxRF7Q2iOXh0xbdN4f2X2uz+YPyy7qj+WQoch9XCPJtkLrO2c14TZFuk7ztV9DgxUNqKPLIASwkkrjIwjAPwN7hVFu4Nhx44smDaHrL8uMydaaR4akD9MwCsbE9qPBpUX59SJaQgPX04NlUvgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Tags = _t]),
PreviousStep = Table.TransformColumnTypes(Source,{{"Tags", type text}}),
//THIS IS THE CODE HERE
ToJson = Table.AddColumn(PreviousStep, "json", each Json.Document("{"&[Tags]&"}")),
RecFields = List.Union(Table.TransformColumns(Table.SelectColumns(ToJson, {"json"}), {{ "json", Record.FieldNames, type list}})[json]),
CorrectTags = Table.ExpandRecordColumn(ToJson, "json", RecFields),
//Choose the one down if you'd like the results in a new table.
//CorrectTags = Table.FromRecords(ToJson[json], RecFields, MissingField.UseNull),
#"Changed Type" = Table.TransformColumnTypes(CorrectTags,List.Transform(RecFields, each {_, type text}))
in
#"Changed Type"
BeforeAfter
Hello all, Smauro Mariusz Anonymous
I have this message:
{"error":{"code":"ModelRefresh_ShortMessage_ProcessingError","pbi.error":{"code":"ModelRefresh_ShortMessage_ProcessingError","parameters":{},"details":[{"code":"Message","detail":{"type":1,"value":"We cannot convert the value null to type Record."}}],"exceptionCulprit":1}}} Table: Usage details.
The code:
let
Source = AzureCostManagement.Tables("Enrollment Number", "xxxxxx", 4, []),
usagedetails = Source{[Key="usagedetails"]}[Data],
PreviousStep = Table.TransformColumnTypes(usagedetails,{{"Tags", type text}}),
//THIS IS THE CODE HERE
ToJson = Table.AddColumn(PreviousStep, "json", each Json.Document("{"&[Tags]&"}")),
RecFields = List.Union(Table.TransformColumns(Table.SelectColumns(ToJson, {"json"}), {{ "json", Record.FieldNames, type list}})[json]),
CorrectTags = Table.ExpandRecordColumn(ToJson, "json", RecFields),
//Choose the one down if you'd like the results in a new table.
//CorrectTags = Table.FromRecords(ToJson[json], RecFields, MissingField.UseNull),
#"Changed Type" = Table.TransformColumnTypes(CorrectTags,List.Transform(RecFields, each {_, type text}))
in
#"Changed Type"Thanks...
Just replace nulls in [Tags] ...
Table.TransformColumns(usagedetails,{{"Tags", each if _ = null then "" else _, type text}})
Hello
Thanks for your answer. I found a solution today:
let
Source = AzureCostManagement.Tables("Enrollment Number", "xxxxx", 8, []),
usagedetails = Source{[Key="usagedetails"]}[Data],
#"Added Custom" = Table.AddColumn(usagedetails, "Tags JSON", each Text.Combine({"{ ", [Tags], " }"})),
#"Parsed JSON" = Table.TransformColumns(#"Added Custom",{{"Tags JSON", Json.Document}}),
#"Tags JSON développé" = Table.ExpandRecordColumn(#"Parsed JSON", "Tags JSON", {"CMDB", "Contact", "Environment"}, {"Tags JSON.CMDB", "Tags JSON.Contact", "Tags JSON.Environment"})
in
#"Tags JSON développé"