Using Date formulas in power Query (1D, 2W, etc)

[Gringohekus]

Hi, I am developing reports from Business Central where payment terms,leadtimes are defined in Date Formulas.

How can I use these formats in Power Query formulas?

https://docs.microsoft.com/en-us/dynamics365/business-central/ui-enter-date-ranges

https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/methods-auto/datef...

Thanks,

[AlexisOlson]

It would help if you could be more specific. Can you give some example inputs and expected outputs?

How to Get Your Question Answered Quickly

[Gringohekus]

@AlexisOlson 

Sure.

I want to calculate in my query the due of each PO line based on the Requested Receipt Date of the PO Line and Payment Termformula from the PO Header:

Obviously it is not working.

I also tried the Expression.Evaluate  formula which is used to to calculate this formula in AI language  But it also not works.

[BA_Pete · Accepted solution]

Hi @Gringohekus ,

You could add a custom column something like this:

Payment_Terms_Days =
if Text.Contains([Payment_Terms_Link.Due_Date_...], "W")
then Number.From(Text.Select([Payment_Terms_Link.Due_Date_...], {"0".."9"})) * 7
else Number.From(Text.Select([Payment_Terms_Link.Due_Date_...], {"0".."9"})

This should give you a column that converts your 30D/2W column into number of days that you can use for further date calculations. It's also easy enough to see the formula structure I've used if you wanted to add conditions for 'M' or 'Y' etc.

I get the following output:

Pete

[AlexisOlson]

Power Query uses the M language whereas the links you gave in your post are related to the AL language.

I'm not aware of any M functions that automatically interpret these types of input so I think you'd have to define how to parse these yourself.