Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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 s...
Need Help
Anonymous's avatar
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's avatar
    hugoberry
    Icon for Responsive Resident rankResponsive 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's avatar
      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.

      • hugoberry's avatar
        hugoberry
        Icon for Responsive Resident rankResponsive Resident
        9 years ago

        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.

  • PBILover's avatar
    PBILover
    Icon for Helper V rankHelper V
    6 years ago

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

    I am stuck here.

     

  • PBILover's avatar
    PBILover
    Icon for Helper V rankHelper V
    6 years ago

    Anonymous 

    Which work around did you implemented for this?