Forum Discussion

Ray_Brosius's avatar
Ray_Brosius
Helper 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 sprin...
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.

ERD's avatar
ERD
Community 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
Helper 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...