Forum Discussion
Excel 2016- Pivot table and Power query
Hi Kaz28EN , thank you for reaching out to the Microsoft Fabric Community Forum.
Thank you Akash_Varuna for your prompt response.
The error message you're encountering indicates that there is an issue with the connection to the external data source in your Power Query. Specifically, it mentions that the source database or table does not exist, or you do not have access to the data source. Additionally, there is a mention of an argument mismatch in a function.
Please consider following below steps:
- Ensure that the file path in the Power Query is correct and points to the right location. If the file has been moved, update the path in the Power Query Editor.
- Make sure the file or database you are trying to connect to still exists and has not been deleted or renamed.
- Ensure you have the necessary permissions to access the file or database. If it’s on a network drive or SharePoint, verify your access rights.
- Open Power Query Editor -> Data -> Queries & Connections -> right click on your query -> Edit
- In the Power Query Editor, look for the Source step in the Applied Steps pane on the right. Verify that the source path or connection string is correct.
- The error mentions a function expecting between 2 and 4 arguments but receiving 5. Identify the function causing the issue. Review the steps in the query to find where this function is used and correct the number of arguments.
- After making the necessary corrections, click Close & Load to save the changes and load the data back into Excel.
- Go to the Data tab and click Refresh All to refresh the query and the Pivot Table.
- Sometimes, there might be hidden steps in the query that are causing issues. Review all steps carefully.
- If the above steps do not resolve the issue, consider recreating the query
If this helps, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details.
Thank you.
Thank you for your reply unfortunately i think the data base from the person that set the power query up no longer is valid.
Would anyone be able to assist and set up a new one for me or just even a pivot table.
I need a pivot table to show the months and locations of samples carried out with results so we can evaluate failures etc
Hi Kaz28EN , thank you for reaching out to the Microsoft Fabric Community Forum.
Please follow below steps to set up Power Query and Pivot Table:- Ensure your data is in a clean and structured format, such as an Excel table or CSV file. The data should include columns like Month, Location, Sample Results and any other relevant columns.
- Open Excel and go to the Data tab. Click Get Data and select the appropriate data source. Load the data into Power Query.
- Use Power Query to clean and prepare the data. Remove unnecessary columns. Ensure the Month and Location columns are in the correct format. Filter out any irrelevant rows. Click Close & Load to load the cleaned data back into Excel.
- Select the cleaned data in Excel. Go to the Insert tab and click Pivot Table. Choose where to place the Pivot Table.
- Drag the following fields into the Pivot Table. Rows: Month, Location, Values: Sample Results (set to Count or other aggregation as needed). Use filters or slicers to analyze specific subsets of the data.
- If the source data changes, you can refresh the Pivot Table by Going to the Data tab and clicking Refresh All.
If this helps, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details.
Thank you.Hi Kaz28EN , thank you for reaching out to the Microsoft Fabric Community Forum.
If I am understanding it correctly, you want help setting up pivot table and power query. To do this, please follow above steps (of my previous reply).
For your reference I have attached screenshots of doing this based on a sample data.If this doesn’t help, please consider sharing your data if possible.
If this helps, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details.
Thank you.