cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PBILover
Helper IV
Helper IV

not showing all columns in power BI using cosmos db connector if first 1000 rows are empty

Is there any way  to expand ALL columns from a DocumentDB.Contents => Table.FromList source where some columns don't have data in the first 1,000 rows. 

here i have connected to Cosmos DB  and  tried to  manually expand  all colums as well Sorting my data.

I have observed that if first some 1000 + rows are empty for some records then PBIdoes not see it and drops the entire column.

I tried to see by using record.fieldname 

for testing when i have created 2 queries one without filter and other with filter the output is as below 

Query 1                         Query 2

Field a

Field a

Field b

Field b

Field c

Field c

Field d

Field d

 

Field e

 

Field f

 

 

As shown above query 2 result contains newly ADDED fields as this queryy is filtered on newly added field.

Query 1 does not show newly added fields (Field e, Field f) as first 1000 + rows are empty for it.

I observed that PBI only does not see those columns as for some instances JSON properties are empty so it drops these 2 columns.

 

 

Is there any way to tackle this?

 

as the data structure in Cosmos document will frequently  change in source , how to handle this?

1 REPLY 1
v-lili6-msft
Community Support
Community Support

hi  @PBILover 

In power bi query, it will preview first 1000 rows data, as a workaround, you could use different sort for the columns.

and you could just do the transform and when you apply it, it will show all the data in power bi data view.

 

Regards,

Lin

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

Helpful resources

Announcements
Power BI Show Episode 10 Recap

The Power BI Community Show

Watch the playback when Amit Chandak, a Power BI Super User, demos how to use Field Parameters to make reports more dynamic.

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Ignite 2022

What's Next at Microsoft Ignite 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Top Solution Authors