cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

JSON file drill downs

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

 

 

 

 

 

 

5 REPLIES 5
Highlighted
Responsive Resident
Responsive Resident

Re: JSON file drill downs

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

Highlighted
Microsoft
Microsoft

Re: JSON file drill downs

@Babinsky

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

Highlighted
Helper I
Helper I

Re: JSON file drill downs

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.  

Highlighted
Responsive Resident
Responsive Resident

Re: JSON file drill downs

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

Highlighted
Microsoft
Microsoft

Re: JSON file drill downs

@Babinsky

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

Helpful resources

Announcements
Meet the 2020 Season 2 Power BI Super Users!

Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors