Power Query Replace Values with IF statement

[RattlingCarp3048]

New to using formulas in PQ Editor. I'm trying to write an IF statement but I'm not sure what I am missing. This is the formula i tried:
= Table.ReplaceValue(#"Changed Type",each [Date],each if [Date]=null and [Case #]=[Case #] then [Date] else "No Scan",Replacer.ReplaceText,{"Date"}))

Basically if Date = blank/null and Case # is the same as the one above it then replace blank/null with the date above else "No Scan". the date will always be at the top. So 5/21/2024 3:02 needs to be copied down the next 6 rows because its all the same.

Case # Date

AB242437	AB242437 A	5/21/2024 3:02​
AB242437	AB242437 A1	​	5/21/2024 3:31​	5/21/2024 4:52​	5/21/2024 9:00​	5/21/2024 10:18​
AB242437	AB242437 B1	​	5/21/2024 3:42​	5/21/2024 4:52​	5/21/2024 9:01​	5/21/2024 10:21​
AB242437	AB242437 B2	​	5/21/2024 3:42​	5/21/2024 4:52​	5/21/2024 9:02​	5/21/2024 10:23​
AB242437	AB242437 B3	​	5/21/2024 3:42​	5/21/2024 4:52​	5/21/2024 9:03​	5/21/2024 10:26​
AB242437	AB242437 B4	​	5/21/2024 3:42​	5/21/2024 4:52​	5/21/2024 9:04​	5/21/2024 10:28​
AB242437	AB242437 B5	​	5/21/2024 3:42​	5/21/2024 4:52​	5/21/2024 9:05​	5/21/2024 10:30​
AB242446	AB242446 A1		5/21/2024 10:25​	5/21/2024 10:59​	5/21/2024 15:32​	5/21/2024 17:18​
AB242446	AB242446 B1		5/21/2024 10:27​	5/21/2024 10:59​	5/21/2024 15:28​	5/21/2024 17:22​
AB242446	AB242446 B2		5/21/2024 10:27​	5/21/2024 10:59​	5/21/2024 15:29​	5/21/2024 17:25​
AB242446	AB242446 B3		5/21/2024 10:27​	5/21/2024 10:59​	5/21/2024 15:31​	5/21/2024 17:27​

 

[johnny51981]

I don't normally do a formula in a replace value function, I add a New Column, put my formula in, delete the original column(s), and rename the new column.

Keep in mind, when your results are mixed types (in this case you are going to result in Date and Text types), you won't be able to format them correctly and could cause issues further down the line. If this were my report, I would just have a "No Scan" column that only populates the "No Scan" as text, and can be "Scan" if the date population is correct.

 

[RattlingCarp3048]

I don't normally do a formula in a replace value function, I add a New Column, put my formula in, delete the original column(s), and rename the new column.

Keep in mind, when your results are mixed types (in this case you are going to result in Date and Text types), you won't be able to format them correctly and could cause issues further down the line. If this were my report, I would just have a "No Scan" column that only populates the "No Scan" as text, and can be "Scan" if the date population is correct.

Unfortunately i have to have the date. "No Scan" and "Scan" are not options. I can however leave the cell blank rather that putting "No Scan" if that solves the mixed data type issues.

Is there a better way to do this without creating a brand new column? i would really rather not have to create a whole new column, just seems like an unnecessary extra step.... if not, how do i add the AND condition to a conditional column? its not an option for me. All i have is if/then/else.

 

[RattlingCarp3048]

I don't normally do a formula in a replace value function, I add a New Column, put my formula in, delete the original column(s), and rename the new column.

Keep in mind, when your results are mixed types (in this case you are going to result in Date and Text types), you won't be able to format them correctly and could cause issues further down the line. If this were my report, I would just have a "No Scan" column that only populates the "No Scan" as text, and can be "Scan" if the date population is correct.

Also, i did try to add a custom column instead of a conditional column with the below formula which didnt copy down the date. it basically just duplicated the column.

= Table.AddColumn(#"Reordered Columns", "ACS2", each if[ACS]=null and [#"Accession #"]=[#"Accession #"] then [ACS] else [ACS])

 

[alansidman]

Really would be helpful if you included Field names in your columns. Also, show a mocked up solution. There may be a slicker way to do this if we understand fully what the requirements look like. Help us to help you by making it easy for us to understand your needs. Some of us are visual learners.

 

[RattlingCarp3048]

Really would be helpful if you included Field names in your columns. Also, show a mocked up solution. There may be a slicker way to do this if we understand fully what the requirements look like. Help us to help you by making it easy for us to understand your needs. Some of us are visual learners.

i think i figured it out. instead of using replace and if, a much easier way is to group then fill down. thank you for your time