Not all columns expanding because first 1000 fields are null

[Syndicate_Admin]

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,

[Syndicate_Admin]

Hi @dennerj,

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

[Syndicate_Admin]

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.

[Syndicate_Admin]

Hi @dennerj

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

[Syndicate_Admin]

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

[Syndicate_Admin]

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.

[Syndicate_Admin]

@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 @dennerj 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  

[Syndicate_Admin]

Hi,

Is this problem solved? Is there any solution?

[Syndicate_Admin]

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

I am stuck here.

[Syndicate_Admin]

Hey did you got any solution on this?