Yet another Power Query note for Power BI.
I have a string of dates in which I need to create a due date as the 5th of the following month.
In Power Query,
- Choose the Date Column
- Choose Add Column Tab
- From Date & Time
- Date > Month > Month
- Go to the formula box and change
- "Month" to "NextMonthDate"
- "Date.Month([Date]" to "Date.AddMonths([Date],1"
- Change NextMonthDate column to a Date type, if is not already
- Choose the NextMonthDate Column
- Choose Add Column Tab
- From Date & Time
- Date > Month > Month
- Add Column Tab
- Custom Column
- Name = Fifth
- = 5
- Choose the NextMonthDate Column
- Choose Add Column Tab
- From Date & Time
- Date > Year > YEar
- Select Columns for Month, Fifth, Year
- Transform to Text type
- Add Column
- Custom Column
- Name = "DueDate"
- [Month]&"/"&[Fifth]&"/"&[Year]
- Select DueDate Column
- Transform to Date type
There may be a more effective way, but this method seems to work perfectly.
Other methods ...
ReplyDeletehttps://community.fabric.microsoft.com/t5/Power-Query/Create-a-specific-Due-Date-based-on-another-Date/m-p/4157719#M136590