Power Query in Excel issue

[GarethParry]

Hi,

We used to use the legacy microsoft query option in excel to pull data into a spreadsheet. we would then share this spreadsheet with colleagues who could also refrssh the data.

As MS are movinig to Power query we thought we would do the same, however when we share the sheet and other users refesh the data they get asked for log in credentials. Its an SQL datasource with SQL credentials set up so they dont have individual credentials and we do not want to share the log in details

We have looked at pushing out a DSN via intune but this doesnt store the credentials either.

Is there a way of doing this?

thanks

Gareth

[Ilgar_Zarbali · Accepted solution]

In Power Query, credentials are stored "per user" and "not embedded" in the Excel file for security reasons. That’s why others are prompted to log in when refreshing the data.

To work around this securely:

1. "Use a gateway + Power BI service" if you're open to sharing via Power BI – it allows central credential management.
2. Or, consider using a "shared Windows-authenticated SQL account" if your SQL Server allows it.
3. Embedding SQL credentials in Excel is not supported for security reasons.

Unfortunately, Power Query is designed to avoid sharing credentials directly for compliance and safety.

[MCG]

Hi there,

I would create workflow in PowerBI service ( connection to your SQL with all needed credentaials ) and then connect via PQ in Excel

Workflow can be updated on daly basis

br

MCG

[v-sdhruv]

Hi @GarethParry ,

No, you cant pass parameters from Excel to the datasetnot directly. Power BI datasets don’t support dynamic parameter passing from Excel like Power Query in Excel does.Excel allows filtering, slicing, and pivoting within the scope of what's already loaded in the dataset, but you can't pass new parameters to filter the dataset at the source level.If you're staying in Excel and need parameter-driven queries:

You'll need to continue using Power Query in Excel and handle parameter entry manually.
For workspace license-
1.  If the Workspace is Pro Workspace, users will need Pro License
2.If workspace is Premium/My workspace, free users can connect.

Hope this helps!
If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You!

[v-sdhruv]

Hi @GarethParry 
Just wanted to check if you had the opportunity to review the suggestion provided?
If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

[v-sdhruv]

Hi @GarethParry ,

 This is a common challenge when transitioning from legacy Microsoft Query to Power Query in Excel, especially with shared workbooks and SQL authentication.

Power Query stores credentials per user, not per workbook. So, when someone else opens the workbook and refreshes the query, they're prompted to authenticate, because their local credential store doesn't have access to the original SQL login.
As rightly pointed by @Thejeswar , using a Power BI Gateway (On-premises Data Gateway) with Power BI Service,

you can publish the data model to Power BI,  then let up a gateway connection with the shared SQL credentials centrally then the end-users can just refresh via Power BI — no credential prompts.

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

[Thejeswar]

Hi @GarethParry ,

This is how it works. The SQL Server handles authentication to the data in it, so a separate authentication may be required to refresh the SQL data.

Alternatively, you can try using On-premise data gateway to create the authentication at the Power BI Service level. If my understanding is right, this will allow you to authenticate to the SQL server using a centrally managed connection. Once you authenticate to it and share it, if someone tries to refresh the data, the On-premise data gateway should take care of the authentication part for the refresh.

BTW this approach would require you to publish your Power Query into Power BI Service by placing it in a PBIX

Regards,

[GarethParry]

Hi,

I can see how i can do this, but

How do i pass parameters from Excel to the dataset

Do all of the users who will access the information need to have a power bi License?

[Thejeswar]

Hi @GarethParry ,

If you are taking the Power BI Gateway based authentication approach, as your Gateway will already have your credentials authenticated with your dataset, any person refreshing it may not need separate credential / authentication to refresh the dataset.

As for the license part, if your workspace is in Premium Capacity, your users may not need individual license to refresh the dataset from excel. In case if you are in a PRO license, then only users who have PRO License will be able to refresh the dataset

If this answers, kindly "Accept it as Solution" and appreciate with a Kudo !!

Regards,

[GarethParry]

HI,

Not sure i fully understand. we need the query in excel not a power BI report. we can access data in the power BI service from Excel?

thanks