HANA Data and Sharepoint data acting different using same Add Column code in Power Query

[msciarrino]

Hello Everyone,

I have a strange issue I am seeing and I cannot figure out what is wrong or a work around to get it to work. I am connected to a HANA database in power query and a snapshot of that same HANA database in .csv form stored in a sharepoint location. I am apending the two datasets into a new table.

I created a new column that calculates the status of each row using the code below.

= Table.AddColumn(#"Filtered Rows", "Status", each if [PO Date] <> null then "Received" else if [Delivery Date] <> null then "On PO" else if [Required Date] <> null then "Plan" else null)

My problem is that if I use this code on my appended table, everything that was in the HANA database only returns "Received" no matter if there was a null or not in the PO Date column. The Sharepoint data portion works correctly. I created the column on the tables before being appended, and the same result happened, HANA didnt work but Sharepoint did.

I am assuming this has something to do with the null value in the HANA data, but I cannot figure out a way to get this to work correctly. I tried replacing the null with a generic date, but it didnt work either. Any idea on what I can do?

Thanks

[v-xinruzhu-msft]

Hi @msciarrino 

1.Make sure that the data type of the [Po date] is date, if the data type not date, please change the data type is date.

2.If the data type is date , then you mentioned that you change the null value to a normal date, please make sure like the following picture.

Then you need to change your code to the following.

= Table.AddColumn(#"Filtered Rows", "Status", each if [PO Date] <> #date(1888,1,1) then "Received" else if [Delivery Date] <> null then "On PO" else if [Required Date] <> null then "Plan" else null)

If the solution above cannot solve the problem, can you provide some sample data of your [PO DATE]?

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[msciarrino]

Hello, The column is set as a data and when I try to replace the null with 1/1/1888 it does not work. After the replace task, there are still null values in the column.

= Table.ReplaceValue(#"Added Conditional Column",null,#date(1888, 1, 1),Replacer.ReplaceValue,{"PO Date"})

Below is the sample of my PO Date column. This data is from a HANA connection. The same data from sharepoint works perfectly fine.

Thanks

[msciarrino · Accepted solution]

I was able to solve this problem by changing the PO Date Column to Text, Replacing the null with 1/1/1900, then changing the PO Date Column back to date. Then in my new column used this code and it work for everything.

= Table.AddColumn(#"Filtered Rows", "Status", each if [PO Date] <> 1/1/1900 then "Received" else if [Delivery Date] <> null then "On PO" else if [Required Date] <> null then "Plan" else null)

This seems like a bug or something. Not sure why I was able to replace the null as text but not as a date.