Forum Discussion
Ray_Brosius
Helper III
Helper IIIHow 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...
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
Community Champion
Community ChampionHi 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.