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

expand column with json content

Hi ,

 

I have a SQL table with columns and one of the columns contain a json string.  I would like to have a flat table ie to expand the column, however the json varies from field to field.

 

 

 

Tx,

gabor

2 REPLIES 2
MarkLaf Member
Member

Re: expand column with json content

Add a custom column and enter the following:

Json.Document([metadata])

If you are worried about errors (e.g. from instances of [] etc.), you can use the following:

try Json.Document([metadata]) otherwise null

The resulting custom column can be expanded.

 

Community Support Team
Community Support Team

Re: expand column with json content

Hi @faludigabor,

 

Since your json files don't have the uniform fields, you need more work based on the idea of @MarkLaf. Please try it out in this file.

1. Add a customer column with the following formula.

Json.Document([Column2])

expand_column_with_json_content

2. Add the following statement to retrieve all the fields of all the jsons in the Advanced Editor.

#"Expanded Column1" = 
Table.ExpandRecordColumn(#"Added Custom", 
"Custom", 
{"premise_zipcode", "city", "address", "need_invoice","bankcard_number", "seat_zipcode", "note"},
 {"premise_zipcode", "city", "address", "need_invoice","bankcard_number", "seat_zipcode", "note"})

expand_column_with_json_content2

It's easy to replace the NULLs and Errors.

 

Best Regards,

Dale

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