Forum Discussion
Create New Columns Based on Two Date Columns
let fx_months = (s, e) => [tbl = Table.FromColumns({List.Generate(() => s, (x) => x <= e, (x) => Date.AddDays(x, 1))}, {"m"}), group = Table.Group(tbl, "m", {"x", Table.RowCount}, GroupKind.Local, (s, c) => Number.From(Date.Month(s) <> Date.Month(c))), out = Record.FromList(group[x], List.Transform(group[m], (x) => Date.ToText(x, "MMM yyyy")))][out], rows = Table.TransformRows( your_data, (x) => Table.FromRecords({x & fx_months(x[Start], x[End])}) ), result = Table.Combine(rows) in result
jamuka , my code solves your problem, that's what is does. Every M solution has some idea behind it. My idea was
- take your start and end dates
- generate a list of dates from start till end
- create a table out of this list, group this table by year/month and calculate count of rows (days), create a record of these year/month names and number of days as values
- take each original row (record), add a record with year/month and count of days and make a table out of it
- combine tables.
This is pretty much it. It's must be not the very performant to my taste but.. it works.
If you want to understand what's going on in M - I would recommend to read and understand Ben Gribaudo's M Primer. Then read some book. Pay attention to The Definitive Guide to Power Query (M). Those guys are great though I don't share their extensive usage of each ( (_) => ) syntax. But that is to my taste. Don't think, buy this book and it will be a huge step for you. Don't pay much attention to YT blogers practicing PQ user interface solutions - you are limiting yourself to UI. But if UI works for you - why not?
