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

Not all columns expanding because first 1000 fields are null in coming JSON

In following scenario data is coming in JSON format and source is cosmos db.

 

In my old PBIX, new columns which are added currently in the source are not displaying as well the records are not available for expansion .Is it bacause for the new columns , first 1000 rows are null?

 

but when i created second PBIX on the same source and query on latest records it is automaticatically shows me those new columns as well those some columns are available for the expansion(List expansion or record expansion) .

why it is not showing all columns in my old PBIX?

Is anyone have an idea how to tackle this scenario?

does anybody tried to expand JSON dynamically in power BI?

 

Thank you

1 ACCEPTED SOLUTION

Hi @PBILover 

I wrote a function some time ago to expand all fields from a JSON dynamically. But it's not very fast:

 

https://www.thebiccountant.com/2018/06/17/automatically-expand-all-fields-from-a-json-document-in-po... 

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

6 REPLIES 6
Community Support
Community Support

Hi @PBILover,

So you mean your data source has different length records(new append records could be larger than previous) and may cause the recognize data structure issues?

If this is a case, I'd like to suggest you do sorting based on row fields count on your datasource and keep the biggest one on the top, it should prevent the recognize data structure issues.

If you can modify the source data, you can also do this on power query side, add a column and use Record.FieldCount('record') functions to extract the field count column and sorting based on this column before you do data analysis.
Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Thanks for reply .

I am trying to implement your solution.

My problem is the data structure might be changing after some time in my coming JSON (here i am connecting to cosmos db)for example lets say my JSON was  having 10 columns and now it change to 12 columns , so in my case power BI is not recognizing those 2 newly added columns (for those columns first some rows are  NULL or empty as those columns are newly added ) even though i am trying to expand records manually as well dynamically . 

If I do the sorting manually then after some time if my JSON change to structure of 15 columns then for those 3 columns i have to do sorting again.

Is there any way to dynamically expand the JSON in a power BI?

or will it show all new columns added in my JSON even if i am trying to expand records manually.

Thank you

Hi @PBILover,

Unfortunately, power query does not suitable to deal with dynamic data structure datasources.

Its query steps will cache the previous data suitable in query step and reference in the following steps. I haven't found effective solutions to achieve data structure adaptive with changeable datasource records.

@ImkeF any suggestions to deal with dynamic data structure records?

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi @PBILover 

I wrote a function some time ago to expand all fields from a JSON dynamically. But it's not very fast:

 

https://www.thebiccountant.com/2018/06/17/automatically-expand-all-fields-from-a-json-document-in-po... 

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

Super User II
Super User II

Hi please use the "Load more"-button for it:

image.png

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Thanks for reply 

This trick doesnt work for me.

 

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors