Forum Discussion
Using Date formulas in power Query (1D, 2W, etc)
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
Thanks,
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
4 Replies
It would help if you could be more specific. Can you give some example inputs and expected outputs?
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.
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
