cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
leewsimpson Frequent Visitor
Frequent Visitor

Parsing JSON into multiple rows

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)

 

IDFields

3

{"fields":{"xmaYRYg4-gwN69I":"200","xmaYRYg4-0NnxwQ":"8"}}

 

I would like to extract this into multiple rows:

 

IDFieldNameFieldValue

3

xmaYRYg4-gwN69I200 

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.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

Re: Parsing JSON into multiple rows

@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.
1.JPG

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
Moderator v-yuezhe-msft
Moderator

Re: Parsing JSON into multiple rows

@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.
1.JPG

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)