Forum Discussion

faludigabor's avatar
faludigabor
Frequent Visitor
8 years ago

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

Need Help

2 Replies

  • MarkLaf's avatar
    MarkLaf
    Super User
    8 years ago

    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.

     

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    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