Help Needed: Error Merging Text Columns in Power BI

[PowerBiased]

Hello Power BI Community,

I've encountered a perplexing issue in Power BI Desktop and could use some guidance. I am attempting to merge two tables based on text columns, but despite both columns being of type Text, the operation fails with an error indicating a type mismatch.

Here are the steps I've taken and relevant details:

Objective: Merge Table1 and Table2 using the "contractNumber" column from both tables to combine related records.

Data Types: Confirmed both "contractNumber" columns in Table1 and Table2 are of type Text.

Join Type: Using an Inner Join as it suits my requirement to match records present in both tables.

Error Message: "Expression.Error: Désolé... Nous n'avons pas pu convertir la valeur '[Record]' en type Record. Détails: Value=[Record] Type=[Type]"

Troubleshooting Steps Already Attempted:

Checked for hidden or non-printable characters using Text.Trim and Text.Clean functions.

Ensured case consistency by converting both columns to lowercase.

Refreshed data in Power BI to ensure all changes were applied.

Manually inspected the data for anomalies.

Attempted to recreate the merge step with the same configurations.

Despite these efforts, the merge operation continues to fail. I have not encountered this issue before and have successfully performed similar merges in the past.

Complementary information:

The error only applies to PBIX file, the online service still manages to update daily. Nothing has changed in the semantic model, it just started occurring out of the blue.

Questions:

Has anyone experienced a similar issue or have any insights on what might be causing this error?

Are there any additional troubleshooting steps or alternative approaches you would recommend?

Could this issue be related to a recent update or is there a known bug in the current Power BI version (2.127.1235.0)?

I appreciate any advice or guidance you can provide. Thank you for your help!

Best regards,

[PowerBiased]

I'm not certain, but I'd guess it happens before or during the merge. If I click each steps in the right Query Steps portion of the screen, I end up with the following error :

Also, all the records are expanded in both tables prior to this step.

Thanks

[ray_aramburo]

If you go one by one, in which step does the message first appears?

[PowerBiased]

The one highlighted in the screen capture. It's french for Merged Queries

[ray_aramburo]

Can you share a screenshot of the Merge Queries UI window?

[PowerBiased]

Voila! Thanks

[ray_aramburo]

Would it be possible for you to share the .pbix file? There is definitely something strange going on in one of the previous steps.

[PowerBiased]

Unfortunately, I cant share the file since it contains confidential informations.

Thanks for trying

[PowerBiased · Accepted solution]

Data Privacy Broken: We cannot convert the value "... - Microsoft Fabric Community

This thread finally did the trick... a 2016 issue turned out to be relevant for me in 2024 🙂

[ray_aramburo]

Does the issue appear before or after merging the queries? Can you provide a screenshot of how you are setting up the merge in Power Query?

Records would need to be expanded from the table you want to bring in data as well.