Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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,
Solved! Go to 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
Proud to be a Datanaut!
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
Proud to be a Datanaut!
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.
Check out the July 2025 Power BI update to learn about new features.