Forum Discussion
Solved
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 f...
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"
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.
resourceTable
dateTable
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