JSON file drill downs

[Babinsky]

Hi Everyone, 

I have a question about importing a JSON file so I can access the data to report.  I have the following documentation below on how to do this which was helpful, unfortunately on the last step when the data is converted to a table, certain fields display as 'list' because the file has drill downs.  Does anyone have any suggestions on how to access this data that displays as 'list'?

https://www.mssqltips.com/sqlservertip/4621/using-power-bi-with-json-data-sources-and-files/

I simply want a table like Excel format so I can acccess all fields, however since this file has multiple drill down I cannot.  

Thanks!

- Babinsky

[Eric_Zhang]

@Babinsky

It's all as @hugoberry suggestion. If you could give some sample JSON file, we could provide more intuitive solution.

[Babinsky]

How do attach the file?  I'm seeing photos and video attachment options but no file attachement option?  

Sorry I'm new to the community.  

[Eric_Zhang]

@Babinsky

You could upload the JSON file in a network drive as @hugoberry suggested and share the download link.

[hugoberry]

Maybe @Eric_Zhang can suggest something, otherwise I would recommend some dropbox/onedrive/google drive alternative.

[hugoberry]

There are several things you can try. It all depends on your particular JSON structure (a sample would be usefull to back up the answer with a meaningfull query)

1. If the list have the same structure you can carry on with Expanding the column that contains the list.

2. You can look at using Table.ExpandListColumn in your query

3. A list can be displayed in a table field by feeding Text.Combine function to it

4. Come up with a custom JSON expansion routine

5. This 3 minute video

6. Transform the lists back to JSON with Json.FromValue