cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LadyRobot
Frequent Visitor

API and power query

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)

LadyRobot_0-1664791286968.png

 

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.

 

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

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.

View solution in original post

1 REPLY 1
v-jingzhang
Community Support
Community Support

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.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors