Create query for date scaffolding - Capacity/Utilization Visuals

[skeebo]

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

[jgeddes · Accepted solution]

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"

[skeebo]

Much easier than I thought it would be! THANKS