Forum Discussion
Refreshing data by import modus crash
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?
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/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.
2 Replies
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/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.
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.
