Count Rows error DataFormat.Error: We Couldn't convert to Number

[MarkusEng1998]

I am trying to compare text strings between two fields: Room vs. Template. I am using this suggestion https://community.powerbi.com/t5/Desktop/Compare-two-strings-for-similarity/m-p/290691#M128019

I get the DataFormat.Error when I try to count rows of matches.

I have three fields: RoomValue, TemplateValue and ValueMatch.

    RoomValue (as text) contains the parsed text into separate words.

   TemplateValue (as text) contains the entire text string to compare to.

   ValueMatch is a conditional column. If the RoomValue is in the Template value, return "Match"

                                                           otherwise "Empty"

I think the error relates to the logic of the conditional column RoomValue because I get the same DataFormat.Error when I use the filter box to load more records (List may be incomplete).

If I remove the filter to exclude blank RoomValue, then the row count executes. But I don't want to count blank text.

            = Table.SelectRows(#"Filtered Rows5", each ([RoomValue] <> ""))

[MarkusEng1998]

Thanks, @jbwtp I think you're pointing me in the right direction. The error in the filter combo box is [Expression.Error] cannot convert null to logical. The table merge with the Template table may be the culprit.

[MarkusEng1998]

The workaround is to load the query to a worksheet, and simply use the Excel CountA() or CountIF() to the worksheet.

[MarkusEng1998]

Thanks for the suggestion @jbwtp. Although I filtered the RoomValue for (blank) and (null), I thought I would also try to replace the nulls after splitting the "text string" column. However this did not work either.

1) Parse the Room text string into individual words.    -->   creates columns [RoomValue.1] ... [RoomValue.100+]

2) Most of theses columns are null, replace null with ""

3) Unipivot [RoomValue.1] ... [RoomValue.100+] to [RoomValue]

4) Custom column to scrub the data  Text.Clean(Text.Trim([RoomValue]))

5) Test whether [TemplateValue] contains [RoomValue] with conditional column.   --> [ValueMatch] return Match or Empty

6) Filter [ValueMatch] = "Match"

7) Count Rows.   --> errors out if I filter [RoomValue]<> null and [RoomValue]<>"" before step 1

[jbwtp · Accepted solution]

Hi @MarkusEng1998,

I think you may have null values in one of the source columns, this may cause Text.Contains to fail, hence crashing the entire calculaiton step.

Is your DataFormat.Error says We cannot convert the value null to type Text?

Cheers,

John