Unable to auto refresh Dataset using Power Automate due to Data Source Credentials being greyed out

[Epidot]

Hi,

I have multiple Datasets being refreshed by Power Automate in my organisation where the data source is a Sharepoint List. With these i get the following. All works fine.

However ive now tried to refresh  a data set using power automate where the data source is a Excel file. It doesnt matter if I save this file in Sharepoint folder or my One Drive. I get this issue. Its greyed out.

We do not have a gateway enabled so i have to refresh via Power Automate.  Why are credentials disabled for a Excel file but all is fine for a Sharepoint list?

thanks

[moshema]

My Data source credentials is greyed as well, what can I do?

[Epidot]

Working.  Hope this helps others. Thanks for assistance.

[AmiraBedh]

The credentials are disabled for an Excel file in Power Automate because Excel files are not supported as a data source for Power Automate cloud flows. If you want to refresh them using Power Automate, you need to use a gateway to establish a secure connection to the Excel file.

If you don't have the ability to configure the gateway, you can save the Excel file to OneDrive (for Business) or Sharepoint using the suitable connector in Power Automate and then use Get rows/ Get tables actions, depending on your need >Apply to each > Process the data and use  "Update row" or "Add row"  actions.

Please not that this workaround may not be suitable if you are dealing with  large or complex Excel files

[Epidot]

Thanks for explaining why it isn't working. I'm not sure this will work for me in power automate.  I already use a flow to save the excel spreadsheet to OneDrive. It overwrites itself each hour from an email attachment.

It has no table and I don't need to amend the file so I don't need get rows.i  just need to refresh the dataset on a trigger..without a gateway this is therefor impossible?

[AmiraBedh]

Can you provide more details about your flow ?

[Epidot]

Sure

I have two flows.

The first just triggers when a email is recieved from a folder and saves it to One drive. This contains the spreadsheet without a table. This spreadsheet will overwrite itself in the one drive any time an email is received.

The second refreshes the data set when an email is received from a different inbox folder. It should be this simple and i know would work if the data was instead in a sharepoint list. But just not excel..

Any idea how i can acheive this please? 

thanks

[AmiraBedh]

This is what I understood : you want to refresh the content of a dataset on Power BI service coming from an excel file using a Power Automate flow ? Correct me if I am wrong.

[Epidot · Accepted solution]

Yes however i think i found the solution.  I needed to get data in power bi from 'Web' and insert the local path URL of the spreadsheet found in the file Info section.  This way Power bi points to the correct path without needing a gateway. Now i see options for credentials. I just need to configure credentials and hope it works.

[AmiraBedh]

You can add a new action in Power Automate using the Power BI connect and then select Refresh dataset (provide the workspace where the dataset is)
If the Excel file is stored in OneDrive for Business, you'll need to authenticate your account by clicking on the "Sign in" button and following the prompts.
Depending on your requirements you can choose "Push" or "Streaming" as the refresh type (under "Additional settings")