Finding Null Values to resolve an error

[IanDavies]

I have a dataset and in one column I transform the name of computers to a common standard using this transformation:

= Table.AddColumn(
/* Add a column CleanName, this is used as a key field across all tables. */
#"Set Column Data Types", "CleanName",
/* check to see of the computer name is a SamAccountName if it is use that value */
each if Text.Length([Name]) <= 15 then Text.Upper([Name])
/* check to see if the computername is a Windows Hello Kerberos object, is so use that value */
else if Text.Contains([Name],"AzureADKerberos") then Text.Upper([Name])
/* assume that anything else, longer than 14 characters is a DNS Host name and extract the Hostname portion */
else Text.Upper(Text.BeforeDelimiter([DNSHostName],".")))

it works and as far as I can tell there are no obvious errors in Power Query

Column Quality says there are no empty values:

but when I try and sort on that column I get this error:

clicking the Go To Error button in the message takes me to the transformation above.

Selecting Keep Rows > Keep Errors gives the same error.

Any attempts to merge this table are also met with the "we cannot convert the value null to type logical"

 How can I track down these null values? 

[SamWiseOwl · Accepted solution]

Hi @IanDavies 

Well done for working that out.

You could use the size attribute and set a minimum size:

Or on the Invoked Custom Function step before expanding the rows remove the error rows:

What do you think the solution should have been? Removing the errored tables before it was combined?

Sounds to me like you solved it, mark yourself as solution!

[SamWiseOwl]

Hi @IanDavies 

Before you do this add columns step can you change the column [Name] and [DNSHostName] to TEXT.

This will let you sort, filter etc as the nulls will be convereted to blanks.

Beware when using the preview values at the top of the column, they are only based on the first 999 rows of data.

You could also add a Replace value step before doing your test:

[IanDavies]

Sam,

Thanks for the suggestions, Ive made some mental notes about the column formats, I hadnt spotted that and will update the table. Unfortunately on this occasion neither of your seuggestions worked. Even after changing the column types, I still got the error and I added a replace step but again I still got the error.

Knowing that this worked the last time the data was updated, I rolled back to the last known good state.

This table brings in all files in a folder using

= Folder.Files("filepath")

Having rolled back to a known good state, I introduced this weeks files one at a time. I found the one that caused the issue and established that it was empty. If there is a way in Power Query to manage an idiot putting an empty CSV files into the folder, please do let me know.

Thanks for responding and for pointing out the typing mismatch. I don't think its appropriate to mark it as a solution because it didn't work albeit another good trouble shooting step along the way.