Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Gringohekus
New Member

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

 

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

 

Thanks,

1 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:

BA_Pete_0-1642784891940.png

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

4 REPLIES 4
AlexisOlson
Super User
Super User

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

@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:

Gringohekus_1-1642781314632.png

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.

Gringohekus_2-1642781484460.png

 

 

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:

BA_Pete_0-1642784891940.png

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

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.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.