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.
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.
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.
Hi,
still it takes lot of time below is the particular step which is creating problem. i have 90k line itesm which has a text column i am trying to identify if any keyword match in this sentence. each text consists a one or two sentences.
Any alternative for this 😞
Table.AddColumn(merge, "Keyword_Match", each List.Accumulate(
Table.ToRows(Key_W),
null, // Initialize with null or an empty value
(a, b) =>
if a <> null then // If a match has already been found, keep it
a
else if Text.PositionOf(Text.Lower([T_Merge]), Text.Lower(b{0})) >= 0 then
b{1} // Found a match, return the corresponding value
else
null // No match yet
))Hi vinayaka123,
Thank you for your follow-up. I understand you are experiencing performance issues when matching keywords from a lookup table against a large text column (~90k rows) using List.Accumulate. This slowdown is expected due to Power Query's evaluation of nested operations, especially when a transformation like Table.ToRows() is recalculated for each row.
To enhance performance, I recommend the following optimizations:
Buffer your keyword table using Table.Buffer to ensure it is evaluated only once.- Replace List.Accumulate with List.First to short-circuit once a match is found.
- Avoid repeating operations like Table.ToRows() within the row context.
M Query:
let // Step 1: Buffer the keyword lookup table KeyWBuffered = Table.Buffer(Key_W), // Step 2: Create a function to find the first matching keyword FindKeyword = (txt as text) as nullable text => let lowercaseText = Text.Lower(txt), match = List.First( List.Transform( KeyWBuffered[Keyword], (kw) => if Text.PositionOf(lowercaseText, Text.Lower(kw)) >= 0 then Record.Field( Table.SelectRows(KeyWBuffered, each Text.Lower([Keyword]) = Text.Lower(kw)){0}, "ReturnValue" ) else null ), each _ <> null ) in match, // Step 3: Add the new column with keyword match Output = Table.AddColumn(merge, "Keyword_Match", each FindKeyword([T_Merge])) in OutputIf 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.
