Bug: cannot fuzzy merge two text columns
Hello
I have basically two tables with each one having a field about the country. Both fields are declared as text in the Query Editor.
I am trying then to merge these columns fuzzily. In the query editor i don t get any error message.
Afterwards however I get the following error message:
"OLE DB or ODBC error: [expression error] We only support text columns for fuzzy join operations. The column 'Country1' is not of type text..."
In my opinion, this is a bug. Or do i miss something?
I am running the version 2.68.5432.841 64-bit (April 2019)
Here is the code of my fact table.
let
Source = Excel.Workbook(Web.Contents("https://myhavi-my.sharepoint.com/personal/kahfatt_chan_havi_com/Documents/Power%20BI%20Portal%20Working%20Folder/03%20Data/CSAT%20Incidents%20Requests.xlsx"), null, true),
Incidents_Sheet = Source{[Item="Incidents",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Incidents_Sheet, [PromoteAllScalars=true]),
#"Excl GSOC = yes" = Table.SelectRows(#"Promoted Headers", each ([GSOC] = "--")),
#"Call fIncidentsRequests" = fIncidentsRequests( #"Excl GSOC = yes"),
#"Appended Query" = Table.Combine({#"Call fIncidentsRequests", #"CSAT Requests"}),
#"Changed Type" = Table.TransformColumnTypes(#"Appended Query",{{"Country", type text}}),
#"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{"Country", Text.Trim, type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Trimmed Text",{{"Country", "Country1"}}),
#"Merged Queries" = Table.FuzzyNestedJoin(#"Renamed Columns", {"Country1"}, DimCountries, {"Country"}, "DimCountries", JoinKind.LeftOuter, [IgnoreCase=true, IgnoreSpace=true]),
#"Expanded DimCountries" = Table.ExpandTableColumn(#"Merged Queries", "DimCountries", {"Country"}, {"DimCountries.Country"})
in
#"Expanded DimCountries"
Cheers. G.
8 Comments
v-yulgu-msftMicrosoft Employee
Hi Anonymous ,
I am unable to reproduce this problem on my side. Could you please share some dummy data of country column in both tables so that I can test? If possible you can directly send me the sample PBIX file, do mask sensitive data before sharing.
Regards,
Yuliana Gu
Hi!
I would like to keep this bug report open.
v-yulgu-msft i sent you the file private.
Best regards. G.
Update: i might have found the solution.
In the query, there was still some "null" in the column Country1. I replaced them with "Others" and it is now working.
But, how come, it is possible to set the data type to text, but the fuzzy merge does not consider it because of the nulls. I think the error message should be clearer.
Cheers. G.
HI ALL,
Today i got the same error : "We only support text columns......." while trying to implment fuzzy match in power bi.
I tried to mach columns extracted from excel files. Columns are certainly a text column.
And the funny part is, It doesn't give error while implemented in Query as one of the step.
It gives error when data is getting refreshed in model due to interaction with VISUAL.
Idetally it Should give an error right in place where Fuzzy Mapping function is implemented i.e in power query.
could you please let me know why so..
If it is bug, MS should not release functionlality like this.
Definitely a bug or at least the error message should better reflect the problem
Exact same problem as everyone reporting here
The table preview loads after the Fuzzy Merge but when you load into the data model you get this error message
Fixed by removing the nulls in the column PRIOR to the Fuzzy Merge
The error message is misleading. the solution of Ghuiles works also for me. I have replaced remaining "null" in the column Country used for the fuzzy merge with "Others".
