英文:
How to generate a list of first day of each month between the two dates in power query
问题
在Power Query中，我尝试根据现有的"StartOfDate"和"EndOfDate"列生成行列表。我想要添加一个新列，其中包含如下表所示的日期列表。
| StartOfDate | EndOfDate | New Column |
|---|---|---|
| 2023/1/1 | 2023/2/15 | 列表 |
| 2023/2/3 | 2023/4/2 | 列表 |
在展开列表后，我只需要在"StartOfDate"和"EndOfDate"之间的每个月的第一天。
| StartOfDate | EndOfDate | New Column |
|---|---|---|
| 2023/1/1 | 2023/2/15 | 2023/1/1 |
| 2023/1/1 | 2023/2/15 | 2023/2/1 |
| 2023/2/15 | 2023/4/2 | 2023/2/1 |
| 2023/2/15 | 2023/4/2 | 2023/3/1 |
| 2023/2/15 | 2023/4/2 | 2023/4/1 |
我知道我可以通过{Number.From([StartOfDate])..Number.From([EndOfDate])}生成一个列表，然后通过展开列表获取两个日期之间的所有日期。
但我只需要在日期之间的每个月的第一天...
而且，由于表中已经有很多行，我不想在查询中创建一个步骤来展开所有日期的列表。
有人知道如何做到这一点吗？
谢谢您提前的帮助。
英文:
in power query I'm trying to generate list of rows depending on the existing columns StartOfDate and EndOfDate.
I want to add a New Column which has a list of dates like below table.
| StartOfDate | EndOfDate | New Column |
|---|---|---|
| 2023/1/1 | 2023/2/15 | List |
| 2023/2/3 | 2023/4/2 | List |
After expanding the list, I only need the first day of months between the StartOfDate and EndOfDate.
| StartOfDate | EndOfDate | New Column |
|---|---|---|
| 2023/1/1 | 2023/2/15 | 2023/1/1 |
| 2023/1/1 | 2023/2/15 | 2023/2/1 |
| 2023/2/15 | 2023/4/2 | 2023/2/1 |
| 2023/2/15 | 2023/4/2 | 2023/3/1 |
| 2023/2/15 | 2023/4/2 | 2023/4/1 |
I know I can just generate a list by {Number.From([StartOfDate])..Number.From([EndOfDate])} and then get all the dates between the two date by expanding the list.
But I only need the first day of each month between the dates...
And, since there are already many rows in the table, I do not want to create a step in the query to expand lists of all the dates.
Does anyone know how to to this?
Thank you in advance.
答案1
得分: 4
添加列，自定义列，
=List.Distinct(List.Transform({Number.From([StartOfDate])..Number.From([EndOfDate])}, each Date.StartOfMonth(Date.From(_))))
然后使用新列上方的箭头展开列为行。
英文:
Add column, custom column,
=List.Distinct(List.Transform({Number.From([StartOfDate])..Number.From([EndOfDate])}, each Date.StartOfMonth(Date.From(_))))
then expand the column into rows using the arrows atop the new column
答案2
得分: 2
仅供娱乐，另一种使用 List.Generate() 的方法：
=let s=Date.StartOfMonth([StartOfDate]), e=[EndOfDate] in List.Generate(()=>s, each _<=e, each Date.AddMonths(_,1))
英文:
Just for fun, an alternative using List.Generate():
=let s=Date.StartOfMonth([StartOfDate]), e=[EndOfDate] in List.Generate(()=>s, each _<=e, each Date.AddMonths(_,1))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论