Hex 0A in table

[DouweMeer]

I had some issues with my Power BI Desktop. My pain was that there is this column full with number except a few 'blank' records. At least, there is no value displayed, thus I assumed the field is blank. I applied the logic of isblank, but this resulted 'False'. This made me curious, thus I applied more logic to it:

[field] = blank () : True

[field] == blank () : False

value ( [field] ) = 0 : False

[field] = " " : False

[field] = "" : True

value ( trim ( [field] ) ) = 0 : Error - unable to compare

A colleague suggested to convert to value to Hex. So I copied the table to Excel and entered the value in an online converter. It stated the field is '0A'. Internet tells me that:

The line separator character in QNX4 is a linefeed (hex 0A). Source files separate lines by a single linefeed character, not a carriage return. On input, whenever you enter a carriage return (hex 0D) it is mapped into a linefeed character.

When the editor reads a file it collects characters up until a linefeed, replaces the newline with a null (hex 00) and saves the collected characters as a line in your buffer. The point to note is that the linefeed is not saved. It is stripped on a read and added to the end of each line when the file is written.

I get the sense this is not supposed to end up as the value of a field. Import was done from Excel and is the result of a formule similar to { if ( stuff , true , "" ) }. Any chance you can prevent this symbol from entering into the dataset? 

[v-qiuyu-msft]

Hi @DouweMeer, 

Please use some dummy data to create a report which can show the issue. Then upload it to your OneDrive and paste the share link here. 

Best Regards,
Qiuyun Yu

[DouweMeer]

Hello Qiuyun Yu

@v-qiuyu-msft 

I've created a file that reproduces the issue. My company put a lot of limitations on what can be send. Can you give (/pm) me an email address that I can sent it to? 

With kind regards,

Douwe

[DouweMeer]

@v-qiuyu-msft 

It seems to be caused by transformating a list of Excel files into a single file via the folder function from Get Data. The null value seems to be replaced by the 0A symbol. 

First transformation in the Transform file group :

let
Source = Excel.Workbook(Parameter4, null, true),
#"Contract Data_Sheet" = Source{[Item="Contract Data",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"Contract Data_Sheet", [PromoteAllScalars=true]),
#"Cols" = Table.ColumnNames ( #"Promoted Headers"),
#"Replace" = Table.ReplaceErrorValues ( #"Promoted Headers" , List.Transform ( #"Cols" , each {_ , "!"}))
in
Replace

Second transformation to combine them all :

let
Source = Folder.Files("X:\"),
#"Filtered Rows" = Table.SelectRows(Source, each not Text.Contains([Name], "DQ")),
#"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (4)", each #"Transform File (4)"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (4)"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (4)", Table.ColumnNames(#"Transform File (4)"(#"Sample File (4)"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Contract ID Product ID", type any}, {"Custo. ID", type any}, {"Nom client", type any}, {"Contract ID", Int64.Type}, {"Client Final", type any}, {"Product ID", type text}, {"Description", type any}, {"Date Debut DPS", type any}, {"Date Fin DPS", type any}, {"AC 2019 each 1000", type any}, {"Dealer Cost 2019 each 1000", type any}, {"Quantité", type any}, {"Pays", type any}, {"Commercial", type any}, {"CONCA", type any}, {"Account Manager", type text}, {"Correct AM", type any}, {"Pieces", type any}, {"Rev", type any}, {"Contract Rev", type any}, {"List price 2020", type any}, {"List Price 2020 Increase", type any}, {"Suggested Increase by Pricing", type number}, {" Pricing Recommendation", type text}, {"Adjusted Cost Actual Increase (%)", type number}, {"Adjusted Cost Actual Increase (amount)", type any}, {"2020 Adjusted Cost (NEW) each 1000", type any}, {"Difference Adjusted Cost Increase (%) (Actual- Suggested)", type number}, {"Increase %", type any}, {"New Rev AC", type any}, {"New Contract Rev", type any}, {"Contract Increase", type any}, {"ACTION by AM", type text}, {"AM Comments", type text}, {"Comment by Regional Manager", type any}, {"Pricing member", type any}, {"Contract Status", type any}, {"Item Status", type any}, {"Renewal Priority", type text}, {"LHF", type text}, {"Classification", type text}}),
#"Filtered Rows1" = Table.SelectRows(#"Changed Type", each [Contract ID Product ID] <> null and [Contract ID Product ID] <> ""),
#"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows1",{{"Custo. ID", type text}, {"Contract ID", type text}, {"Date Fin DPS", type date}, {"Date Debut DPS", type date}})
in
#"Changed Type1"

[DouweMeer]

@v-qiuyu-msft 

I've recreated the issue outside the source file. The Null column returns the 0A symbol again. 

Source with show formulaResult in Power BI after transformation from 'folder' function

[v-qiuyu-msft]

Hi @DouweMeer, 

The Null column value in Excel is a empty string. In DAX, not all functions treat the nulls and empty string as the same.

For ISBLANK() function, it returns true when the referenced column value is null. In your scenairo, as the Null column values is not a null actually, it returns 0. You can use BLANK() function to get true result as this function treat the nulls and empty string as the same. 

Reference: 

https://docs.microsoft.com/en-us/dax/blank-function-dax

https://docs.microsoft.com/en-us/dax/isblank-function-dax

Best Regards,
Qiuyun Yu

[DouweMeer]

@v-qiuyu-msft 

But it isn't entered as a null value. The blank column is entered as a null value... There is nothing at all at the 'null' column. 

[v-qiuyu-msft]

Hi @DouweMeer, 

Please share your Excel file with us. You can upload it to your Onedrive and paste the share link here. Do remove sensitive data before sharing. 

Best Regards,
Qiuyun Yu

[DouweMeer]

@v-qiuyu-msft 

Can we have a chat to point out the issue. I had yesterday an course at Microsoft in Schiphol given by a thrid party and they acknowledgded the issue and gave me a work around. Still it is a problem. Can we fix it?