Unpivot column query not working for MS Access database source (works perfectly with excel)

[AmyParker]

Unpivot column query is not working for MS Access database table (or working too slowly that it fails). The same query works perfectly when the same data is loaded in from excel.

full data set is 700 rows/ 60 columns

update: I tried with a reduced dataset (700 rows/1 column) and got the following message:

Failed to save modifications to the server. Error returned: 'There's not enough memory to complete this operation. Please try again later when there may be more memory available. '.

From my understanding the problem is being caused becase it can't complete the query at source in Access and creates a temporary file instead (where as excel works at source)? 

wondering if there is a workaround for this as I don't want to abandon MS Access as the datasource.

[V-lianl-msft]

Hi @AmyParker ,

Firstly, do you use 32 bit or 64 bit Power BI Desktop? Switch to use 64 bit Power BI Desktop if it is possible.

Secondly, turn off the following options in your PBIX file.

Thirdly,how much RAM on  your machine? Do you run other applications at the same time?

At last,how many columns in your tables you are loading?  If you have more than 15 in a large table, then try removing columns you don’t need

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.