Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
Hi @PBILover
I wrote a function some time ago to expand all fields from a JSON dynamically. But it's not very fast:
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
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
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
Hi @PBILover
I wrote a function some time ago to expand all fields from a JSON dynamically. But it's not very fast:
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
Hi please use the "Load more"-button for it:
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |