direct query + excel refresh issue

[juju63]

hello all,

I built reports that have several datasources coming 

- 1 one comes from a powerbi dataset using POWERQUERY

- others are from EXCEL (from sharepoint, but trying otherwise didn't solved the problem)

I first start by downloading the pbi dataset, then I import the excel file and get this message 

translation : A DirectQuery connection is required. To make changes to your model (for example, renaming columns and adding data from multiple sources), you must switch to a DirectQuery connection. This requires adding a local template to your file and is a permanent change

and when I publish on pbi service 

once again translation 😊 : Power BI Publishing This dataset contains calculated tables or columns that reference remote tables, resulting in refresh failures after publication. Do you really want to continue publishing? 

and indeed ==> no auto update possible. I can manually refresh and republish pbix in service, but that's not what I call a smart way ...

is there a clever (and hopefuly easy) way to troubleshoot this ?

[v-xuxinyi-msft · Accepted solution]

Hi @juju63 

Thanks for the reply from djurecicK2  and SaiTejaTalasila , please allow me to provide another insight:

According to your error code “This dataset contains calculated tables or calculated columns that reference remote tables”. This indicates that you have encountered a restriction on the calculated table or calculated column that references a remote table.

Did you connect to the dataset in direct query mode in Power BI Desktop and then create the calculated table?

If so, could you please check to see if a calculated table was created in it? Calculation tables are an import mode that can cause refresh failures.

Also you can check if you meet another limitation in this:

Solved: Composite Models using Power BI datasets, Calculat... - Microsoft Fabric Community

Use composite models in Power BI Desktop - Power BI | Microsoft Learn

Best Regards,
Yulia Xu

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

[juju63]

Hello v-xuxinyi-msft

so according to what I saw in the hyperlink, there's no solution on this topic ...

how letdown it is ...

[SaiTejaTalasila]

Hi @juju63 ,

You can create a composite model (mixed) and you can build report on top of it.I hope it will work.

Thanks,

Sai Teja 

[juju63]

Hello Sai Teja,

I misspelled instead of POWERQUERY, I wanted to say DIRECTQUERY. see the screenshot of my connections

I think it's already a composite model ...

[djurecicK2]

Hi @juju63 ,

 This is normal behavior. When you create a report that uses a published Power BI Semantic Model as the data source, the storage mode will be live connection. When you add excel, you can no longer use live connection mode and must use Direct Query.

From Power BI Desktop, you can click Make changes to this model in the bottom right

Here is additional information:

https://learn.microsoft.com/en-us/power-bi/connect-data/service-live-connect-dq-datasets

[juju63]

hello djurecicK2,

I can't find any trace of the "Make change to this model"