API and power query

[LadyRobot]

Hello all, 

I have API connected to the Power BI and when I would like to add direct query it is not bringing it into as the query is contains tranformations ( changed name of the column and I get rid of some days of the data which i do not want to use)

I am wondering how can I get the data work without the IMPORT MODE as this force me to update the dashboard daily, we are trying for automatization, can anyone give me a guide or hint? 

Thank you in advance.

[v-jingzhang · Accepted solution]

Hi @LadyRobot 

When using DirectQuery, any transformation will need to be applied on every query to the underlying source, rather than once on data refresh, so they're limited to those transformations that can reasonably be translated into a single native query. If you use a transformation that is too complex, you receive an error that either it must be deleted or the model switched to import. 

If you must use DirectQuery, you need to perform those transformations in the data source in advance. If you use relational sources, like SQL Server, you can try creating a view in the database to change column names and filter data. Then connect to this view in Power BI. 

If you accept Import mode, you can publish the report to Power BI Service and set a scheduled refresh for the dataset. If the dataset connects to on-premises data sources, you need to set up an on-premises data gateway and use it to refresh the dataset. 

Configure scheduled refresh - Power BI | Microsoft Learn

On-premises data gateway - Power BI | Microsoft Learn

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.