dee
dee

Reputation: 147

PowerBI - Create a table of dates based off a date range within another data set

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!

enter image description here

********
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

Answers (1)

horseyride
horseyride

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

Related Questions