Refresh taking forever for only 2 columns

[adam_mac]

Hi, a topic I’m sure has been covered a million times.

I am drawing in data from an OData connection and have created a model with only 2 columns, an ID column and a Country column (I removed 99% of the other columns).

When I refresh the data however, it still takes forever to complete, about 700+mb to load. In all there is just over 90K rows, so this seems unnecessarily large. Is there something I am doing wrong? Any advise much appreciated. 

[v-yingjl]

Hi @adam_mac ,

If the source table was loaded or quoted multiple times before filtering columns, you can try to use Buffer functions like Table.buffer() etc. to buffers it in memory to improve performance.

Refer:

1. Table.Buffer for cashing intermediate query results or how workaround Unnecessary Queries Issue  
2. Performance tip for List.Generate (1): Buffer your tables in Power BI and Power Query 
3. Improving Power Query Calculation Performance With List.Buffer() 

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

[mahoneypat]

Looks like it is loading the whole table before the select columns step.  Please try the Json.Document(Web.Contents() approach described in this article with a $select clause for your two columns.  If it works, please report back with how much faster it is.

How to retrieve OData v4 fast in Power BI - Microsoft Dynamics CRM Community

Pat

[adam_mac]

thanks yes this make sense as to why it is taking so long. I have given the json technique a go but cant seem to make it work. I believe its because the CRM i am pulling from has no API and the URL i am using is https://my000000.crm.ondemand.com/sap/byd/odata/v1/c4codata/ which has odata stated in it.