Forum Discussion

Ray_Brosius's avatar
Ray_Brosius
Icon for Helper III rankHelper III
4 years ago
Solved

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?

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

 

 

Need Help
Tips and Tricks
  • ERD's avatar
    ERD
    4 years ago

    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.

3 Replies

  • ERD's avatar
    ERD
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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's avatar
      Ray_Brosius
      Icon for Helper III rankHelper III
      4 years ago

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