Data not loading from Power query in excel

[vinayaka123]

Hi team,

I have prepared this excel file which has power query with the help of this forum but it is not loading the data as there is fuzzy vlookup and more records in concur file. Can someone please help me on this. Also data file path given inside. is it possible to pick from the excel cell.

Please help me.. not sure how to attach the file here.

[vinayaka123]

thank you so much... still i couldn't.. can I share the all the files with you.. how to share the file

[v-kpoloju-msft]

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.

[vinayaka123]

i have zip file which has 4 excel files.. requesting here for the performance optimization.

[v-kpoloju-msft]

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.

[v-kpoloju-msft]

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.

[v-kpoloju-msft]

Hi @vinayaka123,

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-kpoloju-msft · Accepted solution]

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:

1. Enter File Path in a Cell: Input your desired file path into a cell, say A1.
2. Name the Cell: Assign a name to this cell, for example, SourcePath, using the Name Manager.
3. 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.

[vinayaka123]

Thank you so much for your help.. I have changed background run.. it is working..

[v-kpoloju-msft]

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.

[vinayaka123]

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
))

[v-kpoloju-msft]

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.

1. Replace List.Accumulate with List.First to short-circuit once a match is found.
2. 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

    Output

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.