Forum Discussion
Create query for date scaffolding - Capacity/Utilization Visuals
Hello, I need to create a static table so I can make relationships to a dynamic set for various calculations.
I have created a query/table that generates the unique date values I need but can't figure out how to assign those dates to individual values from another query.
For example I have these resources with a value as it's capacity but I need the dates associated to them:
| Resource | Capacity |
| A | 6 |
| B | 7 |
| C | 8 |
| Date |
| 4/16/2025 |
| 4/17/2025 |
| 4/18/2025 |
| 4/21/2025 |
| 4/22/2025 |
The desired outcome would look like this:
| Date | Resource | Capacity |
| 4/16/2025 | A | 6 |
| 4/17/2025 | A | 6 |
| 4/18/2025 | A | 6 |
| 4/21/2025 | A | 6 |
| 4/22/2025 | A | 6 |
| 4/16/2025 | B | 7 |
| 4/17/2025 | B | 7 |
| 4/18/2025 | B | 7 |
| 4/21/2025 | B | 7 |
| 4/22/2025 | B | 7 |
| 4/16/2025 | C | 8 |
| 4/17/2025 | C | 8 |
| 4/18/2025 | C | 8 |
| 4/21/2025 | C | 8 |
| 4/22/2025 | C | 8 |
One way to do this to add a column to the date table that references the resource table. Expand that column and then sort as needed.
resourceTabledateTable
Example code:
let resourceTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJTitWJVnICsszBLGcgy0IpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Resource = _t, Capacity = _t]), Source = List.Dates(#date(2025,4,16), 5, #duration(1,0,0,0)), #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error), #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Date", type date}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each resourceTable), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Resource", "Capacity"}, {"Resource", "Capacity"}), #"Sorted Rows" = Table.Sort(#"Expanded Custom",{{"Resource", Order.Ascending}, {"Date", Order.Ascending}}) in #"Sorted Rows"
2 Replies
One way to do this to add a column to the date table that references the resource table. Expand that column and then sort as needed.
resourceTabledateTable
Example code:
let resourceTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJTitWJVnICsszBLGcgy0IpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Resource = _t, Capacity = _t]), Source = List.Dates(#date(2025,4,16), 5, #duration(1,0,0,0)), #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error), #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Date", type date}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each resourceTable), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Resource", "Capacity"}, {"Resource", "Capacity"}), #"Sorted Rows" = Table.Sort(#"Expanded Custom",{{"Resource", Order.Ascending}, {"Date", Order.Ascending}}) in #"Sorted Rows"Much easier than I thought it would be! THANKS
