Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
leewsimpson
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
v-yuezhe-msft
Employee
Employee

@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
v-yuezhe-msft
Employee
Employee

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors