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
Anonymous
Not applicable

Not all columns expanding because first 1000 fields are null

Wondering if there is a way to expand ALL columns from a Json.Document(Web.Contents...) => Table.FromList source when some columns don't have data in the first 1,000 rows. 

 

I'm aware of methods such as typing in all the known columns manually, or sorting my data, but I'm hoping there is something that is a little more robust and can accommodate column changes/additions.

 

We have 40+ columns being pulled in and about 1/4 of them may have 1,000+ rows consecutively without data. So those columns are being ignored,

11 REPLIES 11
mheath73
New Member

I put a parmatised query in the API call to make sure I get a record that isn't null, complete the transformation and then remove the paramatised query from the first step.

Anonymous
Not applicable

I kind of got it to work yesterday. I think it was an issue with the system we are using to connect to data in NetSuite. The rest api we are trying out doesnt return properties for a json object if they are null or 0. So there are instances where we get 1,000 json objects with empty (not included) properties so PBI never sees them.

@Anonymous 

Which work around did you implemented for this?

can you can you please explain me, how did you handled this situation?

I am stuck here.

 

Hi @Anonymous

I'm trying to come up with an approach to tablify JSON structures.

Based on some other use cases on this forum I've devised an approach which seems to work.

Can you please have alook if you can run this function against your dataset and check is the results are in a desirable state?

https://gist.github.com/Hugoberry/4ad49f4301edf47fffe2ef06aed61513

 

Thanks

Anonymous
Not applicable

Sorry but this solution didn't really apply. It was more of an issue with how the API returns zero and null fields.

It would really help to have an idea of JSON file that you deal with.

It depends on where you get the errors. I assume the preview isn't helping as the data is limited to a set 500 rows of data or so and most of the values you get are empty.

As for the 40+ columns that you have in your nested JSON/List/record, you can have a function that iterates over all the types in those structures and you can use that output as the input for List.ToTable function.

 

If you don't wont to bother with some OneDrive/Dropbox  solution, for JSON files I can recommend http://myjson.com . Just upload your file there and share the URL. This doesn't require any setup. I've used this service in the past to imitate a webapi response with a JSON output.

v-qiuyu-msft
Community Support
Community Support

Hi @Anonymous,

 

I didn't see the limitation in Table.FromList() restrict blank columns. Regarding your question, would you please share some sample data from our to test?

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-qiuyu-msft

 

Hi Qiuyun Yu,

 

we are facing the same problem in querying data from Marketo using REST API.  If the first few records in a column return NULL, the entire column is droppped without any warning.

 

As @Anonymous mentioned, one work around is to type in all the known columns specifically. However, this presumes knowing what those columns are (which means I need to explore data outside of PowerBI).

 

Any suggestions on how to prevent PowerBI from dropping columns with partial NULL records.

 

Thanks,

Kunal  

Hey did you got any solution on this?

Hi,

Is this problem solved? Is there any solution?

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.

Top Solution Authors
Top Kudoed Authors