Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
msciarrino
Helper I
Helper I

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

 

1 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.

View solution in original post

3 REPLIES 3
v-xinruzhu-msft
Community Support
Community Support

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.

vxinruzhumsft_0-1710141838119.png

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.

msciarrino_0-1710188008278.png

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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.