Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Im parsing the JSON from trello. I would also like to extract custom fields. I get to one point where the custom field data looks like this: (value in one row)
ID | Fields |
3 | {"fields":{"xmaYRYg4-gwN69I":"200","xmaYRYg4-0NnxwQ":"8"}} |
I would like to extract this into multiple rows:
ID | FieldName | FieldValue |
3 | xmaYRYg4-gwN69I | 200 |
3 | xmaYRYg4-0NnxwQ | 3 |
I can use the normal expand - however that 'hardcodes' the field names into table column names. I need to be able to refresh the data and no have the parsing fail with differeent field names.
Solved! Go to Solution.
@leewsimpson,
You can perform these steps in Query Editor(split column, remove columns, unpivot columns, split column, rename columns) of Power BI to get the above result. The steps generate the following code in Advanced Editor, you can copy the following code and paste to the Advanced Editor of a blank query to test it.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlbSUaqOUUrLTM1JKY5RsgKyK3ITI4Mi001008v9zCw9gYIxSkYGBjFKOkhyBn55FeWBYDmLGKXaWqXYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Fields = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Fields", type text}}), #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Fields", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Fields.1", "Fields.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Fields.1", type text}, {"Fields.2", type text}}), #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Fields.1", Splitter.SplitTextByDelimiter("{", QuoteStyle.Csv), {"Fields.1.1", "Fields.1.2", "Fields.1.3"}), #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Fields.1.1", type text}, {"Fields.1.2", type text}, {"Fields.1.3", type text}}), #"Split Column by Delimiter2" = Table.SplitColumn(#"Changed Type2", "Fields.2", Splitter.SplitTextByDelimiter("}", QuoteStyle.Csv), {"Fields.2.1", "Fields.2.2", "Fields.2.3"}), #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Fields.2.1", type text}, {"Fields.2.2", type text}, {"Fields.2.3", type text}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type3",{"Fields.1.1", "Fields.1.2", "Fields.2.2", "Fields.2.3"}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"ID"}, "Attribute", "Value"), #"Split Column by Delimiter3" = Table.SplitColumn(#"Unpivoted Columns", "Value", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Value.1", "Value.2"}), #"Changed Type4" = Table.TransformColumnTypes(#"Split Column by Delimiter3",{{"Value.1", type text}, {"Value.2", Int64.Type}}), #"Removed Columns1" = Table.RemoveColumns(#"Changed Type4",{"Attribute"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Value.1", "FieldsName"}, {"Value.2", "FieldsValue"}}) in #"Renamed Columns"
Regards,
Lydia
@leewsimpson,
You can perform these steps in Query Editor(split column, remove columns, unpivot columns, split column, rename columns) of Power BI to get the above result. The steps generate the following code in Advanced Editor, you can copy the following code and paste to the Advanced Editor of a blank query to test it.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlbSUaqOUUrLTM1JKY5RsgKyK3ITI4Mi001008v9zCw9gYIxSkYGBjFKOkhyBn55FeWBYDmLGKXaWqXYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Fields = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Fields", type text}}), #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Fields", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Fields.1", "Fields.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Fields.1", type text}, {"Fields.2", type text}}), #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Fields.1", Splitter.SplitTextByDelimiter("{", QuoteStyle.Csv), {"Fields.1.1", "Fields.1.2", "Fields.1.3"}), #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Fields.1.1", type text}, {"Fields.1.2", type text}, {"Fields.1.3", type text}}), #"Split Column by Delimiter2" = Table.SplitColumn(#"Changed Type2", "Fields.2", Splitter.SplitTextByDelimiter("}", QuoteStyle.Csv), {"Fields.2.1", "Fields.2.2", "Fields.2.3"}), #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Fields.2.1", type text}, {"Fields.2.2", type text}, {"Fields.2.3", type text}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type3",{"Fields.1.1", "Fields.1.2", "Fields.2.2", "Fields.2.3"}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"ID"}, "Attribute", "Value"), #"Split Column by Delimiter3" = Table.SplitColumn(#"Unpivoted Columns", "Value", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Value.1", "Value.2"}), #"Changed Type4" = Table.TransformColumnTypes(#"Split Column by Delimiter3",{{"Value.1", type text}, {"Value.2", Int64.Type}}), #"Removed Columns1" = Table.RemoveColumns(#"Changed Type4",{"Attribute"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Value.1", "FieldsName"}, {"Value.2", "FieldsValue"}}) in #"Renamed Columns"
Regards,
Lydia
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.