Replace null in a column with date from another column on the same row

[PowerBINoob24]

Again, something that seems like it should be pretty straightforward for me, but it's not.  I have a column "Created date" in my PBI table.  There is another column "Original Date" in the table.  Some of the rows have "null" in the Original Date column.  I need to copy over the "Created Date" from the same row when the "Original Date" shows "null.  

I tried replace value in power query, but that doesn't work for what I'm trying to do. Any thoguhts on how I can get this done?

TIA

[PowerBINoob24 · Accepted solution]

Hello all.  Thank you for the responses.  I needed to use the existing column, so I couldn't use any suggestions that created a new column.  Eventually what I did was manually entered a step in the applied steps in PQ.  = Table.ReplaceValue(#"Reordered Columns",null,each [Created],Replacer.ReplaceValue,{"Original Date"})  That resolved my issue.  Thank you all for your help!

[v-dineshya]

Hi @PowerBINoob24 ,

Thank you for reaching out to the Microsoft Community Forum.

Hi @ryan_mayu , @MasonMA . @Abhilash_P , @Ashish_Mathur , @danextian Thank you for your prompt response.

Hi @PowerBINoob24 , If  @ryan_mayu , @MasonMA . @Abhilash_P , @Ashish_Mathur , @danextian  Response has resolved your issue. Please mark their response as "Accept as solution",  to assist other community members in resolving similar issues more efficiently.

Regards,

Dinesh

[danextian]

Hi @PowerBINoob24 

In the query editor you can do this:

[Colum1] ?? [Column2]

The DAX equivalent would be

COALESCE ( 'table'[Colum1], 'table'[Column2] )

Both checks whether the first column is blank/null then return the second column else the first column.

Note: should be null or blank. Power BI treats a white space differentently.

[Ashish_Mathur]

Hi,

You can also write this calculated column formula in DAX

=if(Data[Original data])=blank(),Data[Created date],Data[Original data])

Hope this helps.

[Abhilash_P]

Hi @PowerBINoob24 ,
You can create a new column in both power query and PBI Desktop

Below is the code for creating in power query

[Original Date] = null then [Created Date] else [Original Date]



Below is the code for creating in PBI desktop using DAX

Final Date = 
IF(
    ISBLANK('Table'[Original Date]), 
    'Table'[Created Date], 
    'Table'[Original Date]
)

[MasonMA]

@PowerBINoob24 

Hi, the simpler way would be add one Custom Column with below M, and then remove your 'OriginalDate'

if [OriginalDate] = null then [CreatedDate] else [OriginalDate]

Hope it works:) 

[ryan_mayu]

@PowerBINoob24 

you can try to create a new column in PQ

=if [Original Date] is null then [Created Date] else [Original Date]