Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
My fact table, which comes from a SQL database, contains information about customer bills and payments, e.g., DateBilled, AmountBiled, etc. There is a column for the date when payment was received on the bill, [DateApplied]. For reasons I do not appreciate, the designer of the SQL database populated the [DateApplied] field with a default date of "1/1/1900." This default date appears in the [DateApplied] field in all situations where no payment has been made. Once a payment is made and the date of the payment is recorded, the correct date of payment then appears.
My intent is to remove the default date of 1/1/1900 and leave the field blank when no payment has been made. I tried adding this column:
NewDateApplied = IF([DateApplied]=1/1/1900,BLANK(),[DateApplied])
I get no error message for this new column, but the default date of 1/1/1900 presistently remains unchanged.
Any suggestions would be appreciated.
Solved! Go to Solution.
What I am trying to say is that I find it strange that khorseman's suggestion isn't working for you, since it does work for me.
Could be your Power BI desktop version, or may be something with the column type.
Here is what I did.
= Table.ReplaceValue(#"Changed Type",#date(1900, 1, 1),null,Replacer.ReplaceValue,{"Column2"})
= Table.ReplaceValue(#"Changed Type",#date(1900, 1, 1),#date(1901, 1, 1),Replacer.ReplaceValue,{"Column2"})
Good luck
p.s. an alternative would be to edit the SQL statement according to your needs with a CASE statement, you could also try something like
CASE date='1-1-1900' THAN null ESLE date END AS date
Could be a little different based on your datasource, but you can google the specifics of a case statement for your database.
Would it not be easier to do this in the query editor? There is a Replace Values button there. That way you don't need to create a whole second column. Select the DateApplied column and hit Replace Values. Enter 1/1/1900 in the Value To Find box and null in the Replace With box.
Proud to be a Super User!
Thanks for the reply.
I gave that a try, but the "Replace Value" dialog did not permit me to enter anything other than a Date/Time in the replace box. It would not accept NULL or BLANK.
I tried this in a simple table with a date field. Entered a few dates and did the replce.
It worked just fine.
This is the code that was generated by the replacer:
= Table.ReplaceValue(#"Changed Type",#date(1900, 1, 1),null,Replacer.ReplaceValue,{"Kolom 2"})
Salvolin:
Thanks for the reply. I am not sure exactly what you are suggesting. Is there another way to replace the Date value other than in the Query Editor?
What I am trying to say is that I find it strange that khorseman's suggestion isn't working for you, since it does work for me.
Could be your Power BI desktop version, or may be something with the column type.
Here is what I did.
= Table.ReplaceValue(#"Changed Type",#date(1900, 1, 1),null,Replacer.ReplaceValue,{"Column2"})
= Table.ReplaceValue(#"Changed Type",#date(1900, 1, 1),#date(1901, 1, 1),Replacer.ReplaceValue,{"Column2"})
Good luck
p.s. an alternative would be to edit the SQL statement according to your needs with a CASE statement, you could also try something like
CASE date='1-1-1900' THAN null ESLE date END AS date
Could be a little different based on your datasource, but you can google the specifics of a case statement for your database.
Salvolin:
For some reason, replacing the "1/1/1900" value with "1/1/1901" allowed me to thereafter change the "1/1/1901" to NULL. Go figure. In any event, problem solved.
Thanks for all the attention you paid to this. Above and beyond the call of duty.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.