Forum Discussion
Using Date formulas in power Query (1D, 2W, etc)
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
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
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.
