Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Days Presents between two Dates- Power Query

Hi Experts   I am trying to work out the number days a certain individual | person had consent for between two dates in Power Query...  My Start Date is in the format (capture_day_id) 20190101 - ...
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    Anonymous , Try this for Duration.Days part of your code

     

    Duration.Days(
    #date(Number.FromText(Text.Start([capture_day_id],4)), Number.FromText(Text.Middle([capture_day_id],4,2)),Number.FromText(Text.End([capture_day_id],2)))
    ,
    #date(Number.FromText(Text.Start([expiration_day_id],4)), Number.FromText(Text.Middle([expiration_day_id],4,2)),Number.FromText(Text.End([expiration_day_id],2)))
    )

amitchandak's avatar
amitchandak
Super User
4 years ago

Anonymous , Try this for Duration.Days part of your code

 

Duration.Days(
#date(Number.FromText(Text.Start([capture_day_id],4)), Number.FromText(Text.Middle([capture_day_id],4,2)),Number.FromText(Text.End([capture_day_id],2)))
,
#date(Number.FromText(Text.Start([expiration_day_id],4)), Number.FromText(Text.Middle([expiration_day_id],4,2)),Number.FromText(Text.End([expiration_day_id],2)))
)

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Amit - getting the following error..

     

    List.Dates(
    [capture_day_id],Duration.Days(
    #date(Number.FromText(Text.Start([capture_day_id],4)), Number.FromText(Text.Middle([capture_day_id],4,2)),Number.FromText(Text.End([capture_day_id],2)))
    ,
    #date(Number.FromText(Text.Start([expiration_day_id],4)), Number.FromText(Text.Middle([expiration_day_id],4,2)),Number.FromText(Text.End([expiration_day_id],2)))
    ),

    #duration(1, 0, 0, 0))