Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PBILover
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
v-shex-msft
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

ImkeF
Super User
Super User

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.