Replace Null value to today's date in power query

Hi,
i would like to replace Null values with today’s date.
i have table with effective_day_date and Expiry_day_date. Expiry_day_date has Null values and i would like to replace null with today’s date.

Effective_day_date has: jan2009 and jan2019
Expiry_day_date has : dec2018.

what explains this is product from jan2009- dec 2018 is effective and 2019 jan will be expiry or in effective.
if user select calendar date slicer may 2009 - dec2018 still it should show effective dates data but it’s not showing. so what am trying to do is transform those dates into a number and the create a list or sequence of numbers based on that effective date and expiry date. since expiry date fields having Null values i need to replace that with Today’s date or any future date and then i can use that custom column to join with date dimension table.

this is all my theory but in order to do that i need to know how to replace null with today date? ’

any thoughts or suggestions is much appreciated.

Batch Routes_test.pbix (302.7 KB)
Thanks,
Priya

@Priya,

This should work for you:

  1. Go into Power Query, right click on the Expiry_day_date field and select Replace Values. Enter any date into the second box. For purposes of example, I entered 6/3/20 but any date will work - it’s just a placeholder for the moment:

image

  1. Go up to the formula bar and replace #date(2020, 6, 3) with DateTime.FixedLocalNow()

  1. Change the field type to date, and you should be good to go.

image

  • Brian
2 Likes

@Priya,

In rereading your original post, I’m now not sure whether you wanted the null replaced by today’s date (6/3/20, static) or the dynamic computation of “today” each day moving forward (6/3/20 today, 6/4/20 tomorrow, etc., dynamic). I had initially assumed the latter, but if I’m wrong, just stop after step 1, and you’re done.

  • Brian

Hi Brian,

Thanks for your quick response.
Yes, i need dynamic computation of Future dates (6/3/20 today, 6/4/20 tomorrow, etc., dynamic).

Thanks,
Priya

Hi Brian,

they have changed the requirement ignore my previous question which was solved and they need something else now.

here is something that am looking…

i have a table let say “Product Rules” with Product Id,Product name, effective_date, Expiry_date

something like this:
Updated_table

All i need is ,

  1. the custom column with the list of all dates from “1/1/2009 - 12/31/2019” as Effective _date and
  2. when user select date slicer(date dimension table) and pick randomly 3/21/2009 or any month or any year or any day between 2009 to 2018 Expiry_date column should show as blank Except for product Id (745,746,748,749) should show its Expiry_date as 12/31/2018 but when user select date slicer from 2019 or 2020… these products are Effective and should also show blanks in Expiry_date column. because it doesn’t have any Expiry_date . i hope am clear.

little more explanation if need: since Expiry_date 12/31/2018 have only for 4 Product Id. and theoretically Product from 2009 are effective till 12/31/2018 from 1/1/2019 is in effective or expired. and product from 1/1/2019… are all Effective they don’t have Expiry_date.

any help will be much appreciated.

Thank you.

Hi @Priya, we’ve noticed that you posted on an inactive and previously tagged solved topic. For more visibility please start a new topic within the forum. You may check this how-to guide for reference - How To Use The Enterprise DNA Support Forum How To Use The Enterprise DNA Support Forum

@Priya,

I’m happy to assist on this one, but in addition to starting a new topic can you please do the following to ensure you get the best possible solution:

  • post your sample data in Excel or PBIX form
  • post a mockup of what you would like the final visual (i.e. solution ) to look like

Thanks very much.

– Brian

Thanks BrianJ for your time.

Here is the PBIX file:
Sample_EDNA.pbix (116.1 KB)

Here is my final visual:

dynamic date slicer interacting the table: user selecting any dates between 2009-2018 it show show something like this:

image

dynamic date slicer interacting the table: 2019-2020 we should show in

single table
image

Thanks,
Priya

1 Like

@Priya,

Thanks very much for the sample PBIX and the excellent explanation of your requirement. I think I’ve got this working correctly per your specs:

image

image

Here’s the measure that does the heavy lifting:

Date Filter = 

VAR MinYear = YEAR( [Harvest Min Date] )
VAR MaxYear = YEAR( [Harvest Max Date] )
VAR DateFilterMin = DATE( MinYear, 1, 1 )
VAR DateFilterMax = DATE( MaxYear, 12, 31)

VAR Result =
IF(
    AND(
        SELECTEDVALUE( 'Table'[estimated_date] ) >= DateFilterMin,
        AND(
            SELECTEDVALUE( 'Table'[estimated_date] ) <= DateFilterMax,
            SELECTEDVALUE( 'Table'[expiry_date] ) <= DateFilterMax
        )
    ),
    1,
    0
)

RETURN
Result

I then put this measure into the visual-level filter of the table visual with the following settings:

I hope this is helpful. Full solution file attached below.

1 Like

@EnterpriseDNA,

Can you please split this thread into two separate topics, with the second starting at post #5, since these are completely different questions.

Thanks.

– Brian

Hi @BrianJ,

I have one small doubt. after digging more i guess this is something is more valid.

if i select 10/2/2013 - 7/10/2016 as shown in below screenshot. my result is not correct i want it to follow under 1/1/2009 not in 1/1/2019. if you understand my logic or explanation.

it’s really tricky to catch this point… Products starting from 1/1/2009 to 12/31/2018 are “EFFECTIVE”. that means any date selected in that period of time either 2009 or 2010,2011,2012,2013,2014,2015,2016,2017,2018…start and end date will be Effective untill 12/31/2018.
image

image

similarly after 2018, products from start of year 2019,2020 and so are EFFECTIVE so if user select from 1/1/2019 or 5/10/2019 to 1/1/2020 all all Effective and it should fall under 1/1/2019.
image

image

this above screenshot will give an explanation of those products that falls between start date for example 2016/2017/2018 to end date 2019 it should under 1/1/2009 in estimated column because after 12/31/2018 those products will be expired .

i hope i made it clear.

Thanks,
Priya

@Priya,

Thanks for the clarifications. I’ll dig back into this and get you a revised solution later tonight.

  • Brian

@Priya,

I can see why this was giving you trouble – definitely a tough one to crack. I think I’ve got the logic right, since all five scenarios you provided above check out correctly using the measures below. The first measure pulls the relevant expiration date for each unique set of estimated dates. The second measure codifies your date comparison logic, and generates the 0/1 result that I used to filter the fact table via the filter pane.

Max Expir = 

VAR vTable = 
SUMMARIZE(
    FILTER(
        'Table',
        'Table'[expiry_date] <> BLANK()
    ),
    'Table'[estimated_date],
    'Table'[expiry_date]
)

VAR Result =
CALCULATE(
    SELECTEDVALUE( 'Table'[expiry_date] ),
    FILTER(
        vTable,
        [estimated_date] = SELECTEDVALUE( 'Table'[estimated_date] ) 
    )
)

RETURN 
Result


Date Filter Revised = 

VAR HarvestMin = [Harvest Min Date]
VAR HarvestMax = [Harvest Max Date]
VAR SelectedDate = SELECTEDVALUE( 'Table'[estimated_date] )
VAR LookupExpire =
    LOOKUPVALUE(
        MaxExpirTable[@MaxExp],
        MaxExpirTable[estimated_date],
        SelectedDate
    )

VAR Result =
IF(
    AND(
        HarvestMin >= SelectedDate,
        OR(
            HarvestMin <= LookupExpire,
            LookupExpire =  BLANK()
        )
    ),
    1,
    0
)

RETURN
Result    

I hope this gets you what you need. Full revised solution file posted below. Have enjoyed working with you on this one.

3 Likes

Perfect. i really appreciate your time and your support from you and EDNA team.

Cheers,
Priya

Priya,

My pleasure - glad to hear that worked well for you. Really interesting problem. On first glance, the data look pretty simple and straightforward, but beneath that surface is a pretty complex set of conditionals. DIsentangling all of those was a fun puzzle.

  • Brian
1 Like