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,

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

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.

Hi AnonymousI'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

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.

Not all columns expanding because first 1000 fields are null

11 Replies

v-qiuyu-msft
Community Support
9 years ago
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
- kunaly
  Regular Visitor
  8 years ago
  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
  - AishwariyaV
    Helper IV
    6 years ago
    Hi,
    Is this problem solved? Is there any solution?
Anonymous
Not applicable
9 years ago
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.
- hugoberry
  Responsive Resident
  9 years ago
  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
    9 years ago
    Sorry but this solution didn't really apply. It was more of an issue with how the API returns zero and null fields.
- PBILover
  Helper V
  6 years ago
  can you can you please explain me, how did you handled this situation?
  I am stuck here.
- PBILover
  Helper V
  6 years ago
  Anonymous
  Which work around did you implemented for this?
mheath73
New Member
4 years ago
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.