Hi, I have written a query for a department in work to use to extract information from a schedule they use.
The query works perfectly for me with no errors but when others went to use the query they are getting errors. See below the first error received by other user:
“Query ‘VAV Schedule (horizontal)’ references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.”
After having a quick look online it seemed to possibly be a permissions thing so I changed the data source privacy settings to ‘None’.
After this change, the user now gets the below error:
“Query ‘Equipment Schedule-V18’ (step ‘Removed Other Columns’) used ‘MultipleUnclassified/Trusted’ data when last evaluated but now is attempting to use ‘MultipleUnclassified/Trusted’ data.”
Hoping someone will be able to help me to ensure others in my Organisation can use my query with no errors running for them please?
I have attached the query file here also.
I would be really grateful if someone would be able to take a look please?
My file keeps stopping to upload at 17% so I don’t know how to attach my file here.
I’ve created a link to the excel file on my GDrive:
Your power query is linked to a file on your hard drive. I think the only solution is put the file to sharepoint where you can set up a premission on the sharepoint for each employee that want to have access to the file.
You will have to change to source reference so your power query will be able to read it.
You can also use DataMentor/EDNA AI to help you will a solution to this issue.
The source file for this particular query run is saved on my one drive, but I have parameters set up so that users can enter the file name & file path & it picks that up dynamically. I have tested that particular bit on my local files, my one drive files & online sharepoint files & that all works fine for me.
When others use it, they enter the file path relevant for their one drive or sharepoint.
You are testing on under your name on your hard drive etc. You need to have the file setup for the others has premission on the file that is not your drive. The user selecting the drive on there local drive will still not get the file unless the file on a server is the same for everyone. If you put on a server/sharepoint that user will have access to it.
The plan is for the Query Workbook to live on Sharepoint where individual users can then access it & use it as they require. They can enter the file path to the file they need from the “Details” worksheet.
We have already done this & when others users have tried to use it, they receive the errors within my first forum post, apologies if I have not explained myself correctly. They are not accessing the same file that I have pointed to in my query.
When the Query file is uploaded to Sharepoint, it’s done so with the users having full access to the Excel Workbook.
sorry then i don’t know as …i still believe it has to do with premission access to the file
Have you tried DataMentor/ENDA AI tools or any other AI tool that will help with your error?
DataMentor this what comes up
to your first error
Error Explanation
The error message Query ‘VAV Schedule (horizontal)’ references other queries or steps, so it may not directly access a data source. Please rebuild this data combination. typically occurs in Power BI or Power Query for the following reasons:
Circular References: If the query ‘VAV Schedule (horizontal)’ indirectly references itself through other queries, it can create a circular dependency.
Dependency on Deleted Query: If the query refers to other queries or steps that have been deleted or altered in a way that breaks the reference.
Cross-Query Dependencies: If ‘VAV Schedule (horizontal)’ relies on intermediate steps from multiple queries that are not properly defined or accessible.
Steps to Resolve the Error
Check for Circular References:
Ensure that ‘VAV Schedule (horizontal)’ is not part of a loop of references.
Verify Query Dependencies:
Go to the Query Dependencies view to visualize the relationships between your queries.
Make sure that all referenced queries or steps are valid and accessible.
Simplify and Rebuild:
Break down your query ‘VAV Schedule (horizontal)’ into simpler steps.
Rebuild each step to ensure that all references are clearly defined and correct.
Check for Missing Steps:
Make sure all quoted intermediate steps or queries are available and have not been deleted.
Data Source Access:
Confirm that ‘VAV Schedule (horizontal)’ directly accesses a data source, if required, rather than relying solely on other queries.
Example Workflow
Identify Dependencies:
Query A -> Query B -> VAV Schedule (horizontal)
Simplify Queries:
Query A: Data Source
Query B: Transforms Query A
VAV Schedule (horizontal): Uses Query B
Verify Each Query:
Ensure that each query in the chain is working correctly.
Avoid unnecessary complexity that might confuse the query dependencies.
Tools and Views to Use
Query Dependencies View: View the data lineage to ensure accuracy.
Advanced Editor: Inspect the M code for each query to ensure it is logically correct.
If you follow these steps and practices, you should be able to identify and fix the error in your Power Query setup.
Second error
Error Explanation
The error message Query 'Equipment Schedule-V18' (step 'Removed Other Columns') used 'MultipleUnclassified/Trusted' data when last evaluated but now is attempting to use 'MultipleUnclassified/Trusted' data. could be due to several reasons related to changes in the data source or query logic:
Changes in Data Source:
The structure, schema, or accessibility of the data source might have changed since the last evaluation.
Validation Issues:
The query might be trying to reference fields or columns that no longer exist or have been renamed.
Credential Issues:
The credentials used to access the data source might have changed or need to be re-authenticated.
Query Misconfigurations:
There might be misconfigurations or issues in the query logic that were introduced since the last successful evaluation.
Steps to Resolve the Error
Check Data Source Updates:
Review any changes in the data source that could affect the query. Ensure the structure and fields remain as they were during the last successful evaluation.
Verify Column Names:
Confirm that all columns being referenced, especially during the ‘Removed Other Columns’ step, still exist and have the same names.
Re-authenticate Data Source:
Re-enter credentials if necessary to ensure the data source can be accessed without issues.
Simplify and Validate Queries:
Break down the query into smaller steps and validate each step independently to identify where the issue occurs.
Example Workflow
Identify Current Schema:
Original Columns: Column1, Column2, Column3
Ensure these columns still exist in ‘MultipleUnclassified/Trusted’ data.
2. Check Query Steps:
// Before
let
Source = DataSource,
RemovedColumns = Table.RemoveColumns(Source, {"ColumnX"})
in
RemovedColumns
Ensure that “ColumnX” still exists and is correctly referenced.
3. Re-authenticate: Go to Data Source Settings → Edit Permissions and ensure credentials are correct.
Tools and Views to Use
Query Editor: Verify each transformation step and ensure it is logically correct and that all columns/fields exist.
Data Source Settings: Check and update credentials if required.
Advanced Editor: Inspect and confirm the M code for any inconsistencies or errors.
The first method to fix this problem is to avoid the fnGetParameter function all together and just hard code the file paths. While this works, you cut all dynamic capability from the query that you went to the effort of implementing. In my opinion, this option is awful.
If you want to preserve the dynamic nature of the fnGetParameter function, the only way to fix this error today is to perform the steps below in this EXACT order!
Turn on Fast Combine (Power Query –> Options –> Privacy –> Ignore Privacy Levels)
