Power Query different results

[NicoEnsenada53]

Hello,

I've developped a power query, working fine since weeks on an i5 computer.

However I've asked my CPY to provide me an i7 for "faster" result.

The problem is that with the i7 pc, with the same excel, the same windows, the same query, the results is not the same

ie:

i5 : result is 503 rows

i7: result is 4030 rows

My IT CPY is not able to help on that (they send me google link to "how to create power queries"............).

Any idea of what I should do?

" Post edited to remove the long code, which was not needed "

Please note that I'm not a programmer or a computer guy. 

My knowledge is very limited.

Thnkas for your support

[NicoEnsenada53]

This is the solution,

The threshold set at 1 give me the exact result.

I'm not sure why this makes a difference between the computers. 

Is there an application default settings for that? i can not find.

However, many thanks. This will help a lot.

Thanks for your time.

[PwerQueryKees · Accepted solution]

Not at my laptop, but if you ue the UI to edit your query, there is a cogwheel on the MergedLevel2 step. Click it. There is a parameter there where you give a number for the fuzzyness of the match. 1 = exact match, lower is more fuzzy (IIRC). See here: https://learn.microsoft.com/en-us/powerquery-m/table-fuzzynestedjoin

This is what the docs say:

• Threshold: A number between 0.00 and 1.00 that specifies the similarity score at which two values will be matched. For example, "Grapes" and "Graes" (missing the "p") are matched only if this option is set to less than 0.90. A threshold of 1.00 only allows exact matches. (Note that a fuzzy "exact match" might ignore differences like casing, word order, and punctuation.) The default value is 0.80

[NicoEnsenada53]

hello,

Here are the results:

So the first problem is at step " ExpandedLevel2"

    // Step 5: Merge Level 2 with Level 1
    MergedLevel2 = Table.FuzzyNestedJoin(Level2Result, {"Parent FWBSCode"}, Level1Result, {"FWBS code"}, "Level1", JoinKind.LeftOuter, [IgnoreCase=true, IgnoreSpace=true]),
    ExpandedLevel2 = Table.ExpandTableColumn(MergedLevel2, "Level1", {"FWBS code", "FWBS description"}, {"SUP", "SUP_Description"}),

 during merging I suppose.

Thanks for your help 

[PwerQueryKees]

And Level2Result and Level1Result are identical on both computers?

It is a fuzzy match.... 100% sure the match paremetrs are identical on both computers? I am asking because that is normally the most likely cause.... You might want to try to give a explicit treshold value to see if that helps.

The powerquery versions are identical. The excel versions are unreadable, but I assume these match as well. So software differences seem unlikely...

[NicoEnsenada53]

---

And Level2Result and Level1Result are identical on both computers?

Yes, they are

---

@PwerQueryKees wrote:

100% sure the match paremetrs are identical on both computers?

---

this is the same file, so yes

---

@PwerQueryKees wrote:

You might want to try to give a explicit treshold value to see if that helps.

---

Not sure what this mean.

And yes, same version for MS/Exel/PowerQuery

[PwerQueryKees]

It is impossible to debug this without access to both computers.

I hope you know enough of power query to follow these basic steps:

• Get the 2 computers next to one anther
• On both sides
  • Go in to powerQuery 
  • Do a "Refresh all Previews" 
  • Select each query in turn
  • Select each step in each query in turn
  • Compare results
• When you find the difference, look at the data
  • What are the "extra" rows?
  • Is there a pattern?
  • What type of query step produced the extra results?
• If you don't see it yourself, come back here and give the screenshots, M Code and test data so we can help you....

Kees Stolker

A big fan of Power Query and Excel

[v-kpoloju-msft]

Hi @NicoEnsenada53,

After reviewing the details you provided, I have identified the alternative workarounds that may help resolve the issue. Please follow these steps:

• Ensure both computers are accessing the same data source, as different paths or network drives can cause discrepancies. Consistent privacy levels in Power Query are crucial for accurate data combination, so verify that the privacy settings match on both machines.
• Review each step of the Power Query on both computers to confirm they are identical, checking for any differences in the applied steps. Ensure both computers have the same versions of Excel and Power Query, as updates or version differences can lead to issues.
• Regional settings, such as date formats and decimal separators, can impact data processing. Make sure both computers have the same regional settings. Utilize Power Query's diagnostic tools to identify the exact step causing any discrepancies.
• Verify that the data sources themselves have not changed, as updates or alterations can lead to different results. Be aware that software bugs can occasionally cause unexpected behaviour.

Please refer the below following links for more information:

Dealing with errors - Power Query | Microsoft Learn

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.

Best Regards.