Reputation: 147
I need some help with creating a table based off values from another data set. I have a data set that has a start and end date. Based off the start and end data AND current, how would I create a table that list all the dates within the start and end date range? The screenshot is the data set. The data below is what I am trying to get. Please help if possible!
********
Date
********
3/28/2021
3/29/2021
3/30/2021
3/31/2021
4/1/2021
4/2/2021
4/3/2021
4/4/2021
4/5/2021
4/6/2021
4/7/2021
4/8/2021
4/9/2021
Upvotes: 1
Views: 237
Reputation: 21318
Load into powerquery using data .. from table/range
use the arrow atop the isCurrentSprint column to [x] select Current
Add column .. custom column with formula
= {Number.From([Iteration Start Date]) .. Number.From([Iteration End Date])}
right click the new column and Remove Other columns
Use arrows atop the remaining column to expand to new rows
click column and transform .. data type .. date
file close and load
sample code
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Filtered Rows" = Table.SelectRows(Source, each ([isCurrentSprint] = "Current")),
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each {Number.From([Iteration Start Date]) .. Number.From([Iteration End Date])}),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
#"Expanded Custom" = Table.ExpandListColumn(#"Removed Other Columns", "Custom"),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}})
in #"Changed Type"
Upvotes: 2