SOLVED

New Contributor

# Power Query | Create Column out of two dates

I have differents projects and each has budget, a start date and an end date.

The mission is to create month columns with the respective month name (or number) and distribute the budget on theses columns equally. Every row should be a project.

I am actually strugling with the creation of the columns. Anyone an idea?

4 Replies
best response confirmed by Laurent500 (New Contributor)
Solution

# Re: Power Query | Create Column out of two dates

The real challenge in your scenario is to calc. the #Months between [Start Date] and [End Date] as PQ currently doesn't have a function like Duration.Months or an equivalent to Excel/Dax DATEDIFF. I'm not aware of a perfect solution to this (One on the Net "claims" he figured it out but there are situations where his function doesn't return the correct #Months) ==> In the below query adjust AvgDaysPerMonth at your convenience

With List.Accumulate in attached sample:

(can be shortened)

``````let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
ChangedTypes = Table.TransformColumnTypes(Source,
{{"Project", type any}, {"Budget", type number}, {"Start Date", type date}, {"End Date", type date}}
),
AvgDaysPerMonth = 365/12,
Number.RoundUp(
(Duration.Days(Date.EndOfMonth([End Date]) - Date.StartOfMonth([Start Date])) +1) / AvgDaysPerMonth,
0
), Int64.Type
),
MaxMonths = List.Max(CalcMonths[Months]),
EarliestStart = Date.StartOfMonth(List.Min(CalcMonths[Start Date])),
MonthsList = List.Transform({0..MaxMonths}, each Date.AddMonths(EarliestStart, _)),
(accum,current) as table => Table.AddColumn(accum, Date.ToText(current,"MMM yyyy"), each
if (Date.StartOfMonth([Start Date]) <= current) and ([End Date] >= current)
then [Budget] / [Months]
else null,
type nullable number
)
),
in
RemovedMonths``````

# Re: Power Query | Create Column out of two dates

This looks really good to me.

I did not know List.Accumulate. In the meantime I kind of solved my problem through complicated if then formulas which I obviously did not like at all.

The table you send was of great help. Your solution will expand my horizont for further challenges too.

Thank you very much for your help.

# Re: Power Query | Create Column out of two dates

Glad I could help & Thanks for providing feedback. FYI I edited my post this morning