Authenticating Excel Workbook File - Semantic Model not refreshing due to invalid credentials

[cipherskill24]

Hi,

I have been editing a PowerBI report in PowerBI Desktop and have republished the .pbix file to the Workspace. As a result it has automatically added the two new Data sources I had connected - two Excel workbooks saved in SharePoint (Cloud) - to the existing semantic model (excellent!). The semantic model is now accessing data tables from the Dataverse and also from two Excel workbooks. However, the two Excel workbooks are not connecting and therefore preventing the whole Semantic Model from refreshing now because they are asking for the credentials to be updated (not excellent!).

I accessed the 'Manage Connections and Gateways' area to choose an authentication method but it only gives me one option - 'Windows without impersonation' which I have no idea what this is. It also appears to be saying that the file is actually an 'On-premises' connection vs. a Cloud connection, which is confusing me as the file is saved in the Cloud and not on my local device or a local server (maybe my understanding of the definition of on-premises is incorrect?). 

The workbook files themselves are saved on SharePoint, and after some reading (here: Get data from Excel workbook files - Power BI | Microsoft Learn) I tried adding them to the Semantic Model again by clicking 'New+' and then choosing 'SharePoint' and navigating to the relevant files, in the hopes it would sort out the credentials issue as it's connecting to a SharePoint site that can be accessed by a number of users in the organisation and not just me - it advised me these were 'already added to the semantic model' so the issue is still somehow the authentication.

I have also tried to set up a new Connection directly to the relevant SharePoint site using the 'Cloud' option so it wasnt an 'On premises' connection but this does not connect to the specific Excel workbooks (or I don't know how to do it), just the site, and doesnt appear to have affected anything. I need to do some more learning to find out what some use-cases/scenarios might be for this type of connection, I don't know enough about it.

PowerBI also seems to be indicating that if I keep the 'on-premises connection' I will need a Gateway to connect to the Excel workbooks but they are in the Cloud so I'm not sure what setting up a Gateway and installing an on-premises data gateway would even do, especially a personal one that would only be on my device. The SharePoint site in question was one set up under the MS Teams App structure but I'm not sure if this would have any affect on the issue?

Is there something I have set up initially when creating the Excel workbooks or while working in PowerBI Desktop that means PowerBI is now insisting on using my personal user credentials to access these two workbooks? The name/file path seems to go through my username to the organisation rather than direct throught the SharePoint site managed by my organisation.

I think it seemed to be operating fine from PowerBI Desktop, presumably because the .pbix file is saved locally while you work on it in PowerBI desktop and then re-uploaded when you hit Publish.

Any help would be much appreciated. I'm not able to identify where I've gone wrong or at which stage of the whole operation.

[P00082221]

I need some help to a similar issue. I use to be able to create beautiful reports with PowerBi and link it to data in excel files on OneDrive which would refresh instantly. Now I can't do any of that - I linked a semantic model to an excel in my one drive and it says it refreshes but it actually does not refresh the data. I added a column to the excel file and it is not showing up after many refreshes. 

What is the best way now to use data from an excel file on onedrive and build a report in powerbi service where if I make changes to the data in the excel file it will continue to update. I am so confused, some videos talk about a lake house and microsoft SQL , microsoft fabric. I honestly just want the report to be able to link to the excel file like it use to when we had data sets and update. I don't need a lakehouse. 

Thank you for your help out there ❤️

[cipherskill24]

In your Excel file are you using a Table or just the Excel sheet as is? Because if you have defined a specific Table within the Excel spreadsheet then any columns or rows you add to that Table PowerBI is able to recognise. I read somewhere that if you are using Excel as a data source for PowerBi to always use tables so you can define the Column names and the Table area for PowerBi to "read".

[P00082221]

Thank you for your help, it is a table but everytime I refresh the semantic model it does not add the new column. In excel 365 I even selected all columns and re-formatted as a table but it is still not picking up the new column

[christinepayton · Accepted solution]

Looking at your screenshot, if it's the source you have open, it source starts with C:\, which means it's your local computer - that's why it wants to use a gateway/Windows auth. You may have that folder synced up to SharePoint, but Power BI is looking for it locally. You can update the path it's looking at to the SharePoint path to fix (you won't need a gateway if it's Online) - I have a video on how to do that here if it helps. 

[cipherskill24]

Thank you so much!! That makes perfect sense and your video was exactly what I needed to do 🙂 I have now been able to set up my Refresh schedule again and all the data is working.

[christinepayton]

Awesome!!