expand column with json content

[faludigabor]

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

[v-jiascu-msft]

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])

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"})

It's easy to replace the NULLs and Errors.

Best Regards,

Dale

[MarkLaf]

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.