Reply
Ray_Brosius
Helper III
Helper III
Partially syndicated - Outbound

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

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?

result.jpg

Table DIM_Sprint that shows the information about our Development Sprints

sprint_ref.jpg

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]))

dates.jpg

 

 

1 ACCEPTED SOLUTION
ERD
Community Champion
Community Champion

Syndicated - Outbound

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"

ERD_0-1635617591923.pngERD_1-1635617601891.png

 

 

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

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

View solution in original post

3 REPLIES 3
ERD
Community Champion
Community Champion

Syndicated - Outbound

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"

ERD_0-1635617591923.pngERD_1-1635617601891.png

 

 

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

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

Syndicated - Outbound

@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... 

Syndicated - Outbound

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

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)