Parsing JSON into multiple rows

[leewsimpson]

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.

[v-yuezhe-msft · Accepted 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