Using someone else's credentials

[dave_cousins]

I have a PowerBI semantic model that needs to use someone else's credentials. 

The Power Query looks in a SharePoint folder for XLSX spreadsheets and filters by certain criteria. It then extracts from each of them the table called '_Hours'. The data within the table is a timesheet for hours worked. The filtering by file names should select only spreadsheets that include the table.

My work user account permissions can see only one sub-folder (allocated to me). When used in PowerBI Desktop or published to my Workspace, with my credentials the Power Query works, collecting the _Hours tables from each of my spreadsheets only.

 Our business manager, however, has access to see everyone's sub-folders. So I thought that by changing the credentials to her work login details, the Power Query would see everyone's spreadsheets, and collect the _Hours table from them all. It seems to accept her credentials. 

But it fails to refresh the Power Query in my Workspace with her credentials, and I get this error message:

• Expression.Error: The key didn't match any rows in the table.. Microsoft.Data.Mashup.ErrorCode = 10061. Key = [Item = "_hours", Kind = "Table"]. Table = #table({"Name", "Data", "Item", "Kind", "Hidden"}, {}). ;The key didn't match any rows in the table.. The exception was raised by the IDbCommand interface. Table: Hours.

I had thought the implication was Power Query was finding one or more spreadsheets that did not contain the table '_Hours.' However, I've played with the filtering criteria to ensure that isn't the case.

Any ideas, please?

[dave_cousins]

I haven't yet overcome this.
An indivudal of the team 'shared' a specific spreadsheet from their folder with me as 'read only' - which permitted me to access it, but it didn't retrieve all the data (I suspect because it couldn't recalculate the cell formulae). When he then gave me 'edit' and 'execute' and 'delete' rights, it was able to retrieve all the data.
Still trying.

[v-saisrao-msft]

Hi @dave_cousins,
 

Thank you for providing additional details about your issue. It appears that the problem is related to access permissions and formula recalculation in the Excel files. Let's address this step by step to resolve it. 

I am pleased to hear that granting "edit," "execute," and "delete" rights resolved the issue for the specific spreadsheet shared with you. This indicates that insufficient permissions were likely the root cause. To apply this solution to all spreadsheets within the sub-folders, here’s what you can do: 

1. Please ensure that your business manager's credentials have "edit," "execute," and "delete" permissions for all sub-folders and files within the SharePoint folder. This will enable Power Query to perform the necessary operations to retrieve the data accurately. 
2. Certain Excel files may include formulas that require recalculation upon opening. Please ensure that Power Query can initiate this recalculation, which often necessitates editing permissions. It would be advisable to test this with several files to verify. 
3. Please verify the filtering criteria in Power Query to ensure it accurately targets only the spreadsheets containing the '_Hours' table. This will help prevent errors related to missing tables. 
4. Please test the Power Query using a smaller subset of spreadsheets to ensure it functions correctly before applying it to the entire set of files. 

If this post helps you, please mark it as the solution and give a kudos so that other members of the community can easily find it helpful.   

[v-saisrao-msft]

Hi @dave_cousins,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-saisrao-msft]

Hi @dave_cousins,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my 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-saisrao-msft]

Hi @dave_cousins 

Thanks for reaching out to the Microsoft Forum community.

It appears that you are experiencing an error when attempting to refresh your Power Query in Power BI using your business manager's credentials. This issue seems to arise from Power Query being unable to locate the '_Hours' table in one or more Excel spreadsheets stored in a SharePoint folder. Please follow these troubleshooting steps to help resolve the issue:

• Check that all spreadsheets contain the table named '_Hours'. The error message suggests that Power Query is unable to find the '_Hours' table in one or more spreadsheets.
• Ensure that your business manager's credentials have full access permissions to all sub-folders and files within the SharePoint folder. This includes verifying that they can view all relevant spreadsheets.
• Review and adjust the filtering criteria in Power Query to ensure it accurately identifies and selects only the spreadsheets containing the '_Hours' table.
• If any Excel sheets have been renamed recently, ensure that all references in Power BI are updated accordingly. A change in sheet name can lead to errors if Power BI is trying to access a sheet that no longer exists under its previous name.
• Check that the '_Hours' table is visible. Power Query cannot access hidden tables, so this step ensures that data retrieval is not hindered by visibility settings.

If this post helps, please give us Kudos and consider accepting it as a solution to help other members find it more quickly.

Thank you.

[Wikkleyn_81]

Hi @dave_cousins That error message is never very helpful 🙂 

What I would suggest is using the maanger's credential in Power BI desktop and making sure the data loads in Power Query and each step of the query works.