Forum Discussion
HANA Data and Sharepoint data acting different using same Add Column code in Power Query
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
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.
3 Replies
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.
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
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.
