Forum Discussion

msciarrino's avatar
msciarrino
Helper I
2 years ago
Solved

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 tha...
Need Help
  • msciarrino's avatar
    msciarrino
    2 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
    msciarrino
    Helper I
    2 years ago

    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's avatar
      msciarrino
      Helper I
      2 years ago

      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.