Refreshing data by import modus crash

[MarkVerwer]

My dataset has a gateway to a personal database on a server. By refreshing the data (with modus directquery) it all works fine. Refresh time = 2 minutes or less.

But i had to switch to import modus because of unpivot functions on a reference table.

With this modus, the refresh is crashing (after 45 minutes or so) and the error message is just "Something wrong, try again later".

With details:

URI cluster: WABI-WEST-EUROPE-B-PRIMARY-redirect.analysis.windows.net
Activities-id: 0e942047-0818-473b-88f0-777295674f5c
Request-id: 4562908d-4e20-1613-9b1a-84a9daef9e68
Time: 2022-08-03 10:26:17Z

Im trying to refresh a table with 100k records. 

Is there any solution to unpivot with directquery OR can someone help me with solve the crash?

[v-yangliu-msft · Accepted solution]

Hi  @MarkVerwer ,

The refresh of the two connection modes of Import and Direct query is different.

Direct query:

If you are using the DirectQuery Mode, the report should query data source to get the newest data when you access the report or refresh the report

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-use-directquery

If you are using the Import Mode, it is to import data into Power BI for storage, which is equivalent to a copy or snapshot. When refreshing, go directly to Power BI to get the data without communicating with the data source.

Which Method is Better: Power bi Import vs Direct Query? - Folio3 Dynamics Blog

Solved: direct query vs. import - Microsoft Power BI Community

To use unpivot in Direct Query, you can refer to the following link:

https://community.powerbi.com/t5/Desktop/Unpivot-with-Direct-Query/m-p/909745

https://community.powerbi.com/t5/Power-Query/Unpivot-columns-while-connected-with-Direct-Query/td-p/...

https://community.powerbi.com/t5/Desktop/Direct-Query-Unpivot-Table-SQL-Workaround/m-p/789784

Best Regards,

Liu Yang

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

[MarkVerwer]

Thanks.

First i selected a table with DirectQuery and then "copied" the data to another table with;
= Table.SelectRows(ORDERS, each Date.From([DELIVERYDATE]) > Date.From("01-01-1970")) (with import)

then i could use some pivot and unpivot functions.