Forum Discussion
Data not loading from Power query in excel
Hi vinayaka123,
Thank you for reaching out and providing details about your Power Query setup. I understand you're encountering issues with data not loading, particularly when using fuzzy matching with larger datasets, and you're interested in dynamically setting the file path from an Excel cell.
Fuzzy matching in Power Query can be resource-intensive, particularly with large datasets. Here are some recommendations to enhance performance:
- Adjust Similarity Threshold: Lowering the similarity threshold can reduce computational load. Refer to Microsoft's documentation on how fuzzy matching works in Power Query for guidance.
- Limit Columns for Matching: Use only necessary columns for the fuzzy match to minimize processing time.
- Use Table.Buffer(): Applying Table.Buffer() to the smaller table before merging can improve performance by preventing multiple evaluations. Learn more about this in Power Query best practices.
- Filter Early: Apply filters to reduce the dataset size before performing the merge. This approach is emphasized in the best practices documentation.
Yes, it's possible to set the file path dynamically using a cell value in Excel. Here's how:
- Enter File Path in a Cell: Input your desired file path into a cell, say A1.
- Name the Cell: Assign a name to this cell, for example, SourcePath, using the Name Manager.
- Reference in Power Query: In Power Query, reference this named cell as follows:
Source = Excel.Workbook(File.Contents(Excel.CurrentWorkbook(){[Name="SourcePath"]}[Content]{0}[Column1]), null, true)This method allows you to change the file path directly from the Excel sheet without modifying the query each time. For more details, refer to the Power Query parameters documentation.
Additional steps:
- Check for Errors: In the Power Query Editor, look for any steps marked with errors (indicated by a yellow triangle) and address them accordingly.
- Enable Load to Worksheet: Ensure that the final query is set to load to the worksheet or data model as needed.
- Test with Smaller Data: Run the query with a smaller subset of your data to confirm that the logic works before scaling up.
If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.
Thank you for using Microsoft Community Forum.
thank you so much... still i couldn't.. can I share the all the files with you.. how to share the file
Hi vinayaka123,
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...
Thank you for using the Microsoft Community Forum.
i have zip file which has 4 excel files.. requesting here for the performance optimization.
Hi vinayaka123,
Thank you for your continued effort in trying to resolve this we appreciate the details you've shared so far.
Currently, the forum platform does not support direct ZIP or full file uploads. To help you efficiently, we recommend the following:
Please extract a sample subset of your data from the four Excel files, including only the necessary steps in your Power Query to demonstrate the fuzzy matching and loading issue. Remove any sensitive or unrelated information.While preparing the sample, consider: Testing your fuzzy merge logic on a smaller dataset to identify the bottleneck. Reviewing each query step for transformation overhead and buffering smaller tables as previously suggested (Table.Buffer()). Simplifying your merge keys, if possible, to improve match performance.
Once uploaded, please describe: The expected output. Where the slowness or error occurs (which step, if any, in the query). Any specific goals you're trying to achieve (e.g., dynamic file path and fast fuzzy join).
We will be happy to assist you once we have this information.
Thank you for using the Microsoft Community Forum.
Hi vinayaka123,
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.