How to create a calculated table from two tables using filters and logic

[Ray_Brosius]

I have a question that I hope is easy...  

I am trying to generate the below calculated table.. from the two tables ( DIM_SPRINT and Sprint Dates) which will create a row for each day for each sprint.  Can this be done in Power QUery or do I need to do this in DAX with a calculated table?

Table DIM_Sprint that shows the information about our Development Sprints

Sprint Dates is a calculated table generated by the the DAX formula:

Sprint Dates = CALENDAR(MIN('DIM Sprints'[Start Date]),MAX('DIM Sprints'[End Date]))

[ERD · Accepted solution]

Hi @Ray_Brosius ,

It's very easy to do this in Power Query:

1) Create a new custom column with the code 

{Number.From([start])..Number.From([end])}​

2) Expand
3) Change type to Date

The whole code:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKjFUKDZU0lEy1Dcy1jcyMIKyzSDsWB2gCiOICiN9QyOYCiAbWQXQDCOIKEKBCVQ+FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Sp name" = _t, start = _t, end = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"end", type date}, {"start", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each {Number.From([start])..Number.From([end])}),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}})
in
#"Changed Type1"

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

[Ray_Brosius]

@ERD 

I am reading the Power Query Code you provided and I see what it is doing but I am trying to understand the code itself...

I see that the code here is generating a list for each Sprint record for each day between the Start Date and End Date.. but I'm want to understand more.. 

= Table.AddColumn(#"Changed Type",

                                 "Custom",

                                       each {

                                                  Number.From([Start Date])..Number.From([End Date])

                                                }

                                )

the { } generates records or in this case a list..  

but what exactly is the function or code that "loops" from start to end?  the ".."  ? the double dots are an operator here?

I want to look up in the Power Query Ref docs to better understand..   I knew what I wanted to do but did not know that this method was possible.

Thanks again... 

[Ray_Brosius]

Man that was easy!!! Thanks for that...