Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Ray_Brosius
Helper III
Helper III

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
Super User
Super User

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
Super User
Super User

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!

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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