Using getway with Datasets witch have one excel query

[Stefan_S]

Hello,

I have used my getway for a SQL-Query (CRM) succesful to update a Dataset.

Now I have add a local Excel-Sheet to this Dataset. (I don´t won´t Update this excel-sheet).

After I upload this Dataset to the Power BI Service, the connection to the getway faild.

I´m aware I can´t update the Excel, but the update of the SQL-Query also failed.

How I can set the Excel-Querry to "no update", so the getway will work for the SQL-querry again.

Thanks for your support.

Stefan

[v-yuezhe-msft]

@Stefan_S,

In Power BI Service, it doesn't support to refresh specific data source in a single dataset. And when you combine multiple data sources in a single dataset, all the data sources should be added within gateway in order to refresh the dataset.

Have you added the excel data source within gateway? Could you please post the full error message?

Regards,
Lydia

[Stefan_S]

Hello Lydia,

thanks for your answer. Orginal the excel was local on HDD and there is no getway on my PC. Now I have try to use the Excel-sheet in 365 (Sharepoint). But when I change the Source-Setting in Powerdesk to the Share-point-Link, the system said, i have noch access.

But I have, i tried the link in the browser. There i have Access.

The errore message:

"You don't have any gateway installed or configured for the data sources in this dataset. Please install a new personal gateway or configure the data source for an existing data gateway. "

I have still access to the company gateway, and it still work on other datesets with the same SQL-sources.

Thanks

Stefan

[v-yuezhe-msft]

@Stefan_S,

Based on your description, you combine on-premises SQL data source and SharePoint Online Excel data source in a single dataset, right? If  so, you would need to use on-premises gateway(personal gateway) to refresh your dataset.

Regards,
Lydia

[Stefan_S]

Hi Lydia,

Yes, i have combine this. First I used 8 Entities (tables) based on on-primes-SQL-getway and everything work fine. After I add an excel-based Entity (this information is static, i don´t need to update this information) the getway completly failed in this Dataset. 

I owner of the "company-getway". This getway still work (to update from on-premise SQL) for all my Datasets (without the Excel)

[v-yuezhe-msft]

@Stefan_S,

Do you put  the Excel in SharePoint Online? If so, when you combine the Excel data source and on-premises SQL data source in a single dataset, on-premise gateway(personal gateway) is required to refresh the dataset.

You company gateway should be a on-premises gateway, which can't be used in this case.

Regards,
Lydia

[Stefan_S]

Is there a way to deaktivate the updating of the Excel-entity, so that the SQL part work again?

At the Query Properties there is a field "Include in report refresh", but deactivate this has no effect to my problem.

[v-yuezhe-msft]

@Stefan_S,

As my previous reply, there is no way to refresh specific data source in a single dataset in Power BI Service.

If you don't want to refresh the Excel data source, you need to split your dataset into two datasets, one contains Excel data source and one contains SQL data source.

Regards,
Lydia

[Stefan_S]

But I want to set some relationships between the data (SQL-Exel) to use it in one report.

To my knowledge, I can only connect some dataset in one Dashboards not in one report.

[v-yuezhe-msft]

@Stefan_S,

As my previous reply, when you combine Excel on SharePoint and SQL data sources in a single dataset and want to set schedule refresh for the dataset, you would need to use on-premises gateway(personal mode).

In addition, yes, you can pin visuals of different reports of different datasets to one dashboard.

Regards,
Lydia

[Stefan_S]

Hi Lydia,

i don´t won´t to use SQL-Data and Excel in ONE Dashboard. I have set a relationship between the Entities use it in ONE report. So I can´t see a way to split this in two Dataset.

I  don´t won´t to use a personal mode getway (PC don´t work when i am in holidy)

There should be a way to work with SQL-Getway-data with CRM-Data an a fix data-table (It have not use excel, I am open to change this information in an other form). Is there a way? When yes, how it works.

Best

Stefan

[v-yuezhe-msft]

@Stefan_S,

You set relationship between Excel table and SQL table in Power BI Desktop file and don't want to use personal mode gateway, right?

If so, import the data from Excel to your SQL Server database, then only connect to SQL Server in your dataset. Or you can put Excel file in local drive or networkdirve instead of putting it in SharePoint Online, then combine the Excel file and  SQL data source in a single dataset, this way, you are able to use on-premises gateway to refresh your dataset.

Regards,
Lydia

[Stefan_S · Accepted solution]

I find a way to solve my issue.

I create a new table and copy-past the information into the empty table.

https://www.sqlbi.com/articles/create-static-tables-in-dax-using-the-datatable-function/

Best

Stefan