none
Date function in Power query RRS feed

  • Question

  • Hey everyone,

    I hope you guys can help me with this.

    I need to connect two tables to calculate a due date, which wouldnt be too hard if I dindnt have some suppliers who's due date is always the 25th of the next moth.

    So I planned to just do an if function and if that function finds the text "25." I would use the Date function as I am used to from excel Date(year[invoice date], month[invoice date]+1, day(25))......

    Which dosnt work...

    Can u guy's help me out? I figured out the If function but cant get the date function to work

    Tuesday, November 24, 2015 4:45 PM

Answers

  • Hi Dan. Try inserting a custom column with the following formula:

    = #date(Date.Year([Invoice Date]), Date.Month([Invoice Date]) + 1, 25)

    Ehren

    Tuesday, November 24, 2015 6:21 PM
    Owner

All replies

  • Hi Dan. Try inserting a custom column with the following formula:

    = #date(Date.Year([Invoice Date]), Date.Month([Invoice Date]) + 1, 25)

    Ehren

    Tuesday, November 24, 2015 6:21 PM
    Owner
  • Works like a charm! Thanks so much.

    Is there any page where I can figure out how to turn my usual formulars into these? 

    I only found this "M formular reference" (https://msdn.microsoft.com/en-us/library/mt211003.aspx?f=255&MSPPError=-2147217396) but that didnt explain how to get to that formular. Or did I read it wrong??

    Daniel

    Wednesday, November 25, 2015 6:59 AM
  • That's the official reference for the M language, but I just checked and couldn't find any reference to the #date function. I'll let our content team know.

    Another good source of information on M are books about Power Query.

    http://www.amazon.com/s/?ie=UTF8&keywords=power+query

    Ehren

    Wednesday, November 25, 2015 7:29 PM
    Owner
  • Thanks Ehren.

    now I have the next issue. I want to add days to the invoice date. here my try. but I only get errors ("date not within acceptable boundaries") loosely translated

    #date(Date.Year([invoice date]), Date.Month([invoice date]),  Date.Day([invoice date])+([Days till due]))

    or " + operrator can not be used with Date and Number for 

    #date(Date.Year([invoice date]), Date.Month([invoice date]),  Date.Day([invoice date]))+[Days till due]

    how am i thinking wrong about this?


    • Edited by Dan798 Friday, November 27, 2015 11:32 AM
    Friday, November 27, 2015 11:11 AM
  • LOL got it...

    Date.AddDays([Belegdatum],[Skontotage])

    DOH

    Friday, November 27, 2015 11:33 AM