Explode Date Range

[djm7]

I have a set of data that looks like the following

key    start date        end date         quantity

1        Feb 1 2025      Feb 3 2025     3

2        Feb 1 2025      Feb 10 2025   20

creating the daily quantity is easy, calculate the number of days between start date and end date and divide the quantity by that count.

1       3/3       = 1

2       20/10   = 2

I want to explode out these records into this

key    Date                 Quantity

1       Feb 1, 2025      1

1       Feb 2, 2025      1

1       Feb 3, 2025      1

2       Feb 1, 2025      2

2       Feb 2, 2025      2

2       Feb 3, 2025      2

2       Feb 4, 2025      2

2       Feb 5, 2025      2

2       Feb 6, 2025      2

2       Feb 7, 2025      2

2       Feb 8, 2025      2

2       Feb 9, 2025      2

2       Feb 10, 2025    2

the data is in SQL Server Analysis Services database and imported tables in the semantic model which limits me to not using sql.

also the join ability in power bi is limiting.

[djm7]

question - can I use power query when my data is in SQL Server Analysis Services database ?

[Anonymous]

Hi All,
Firstly  johnt75 thank you for your solution!
And @djm7 , If your data is stored in SQL Server Analysis Services database, you can use power query in import mode to make changes.
However, in the case of a live connection, Power BI connects directly to the SSAS server and the query request is processed by SSAS and the data is not stored in Power BI. Therefore, Power Query cannot be used to perform any transformations or cleansing operations on the data.
All calculations and data summarization are done in SSAS, and Power BI is only used as a front-end presentation tool.

If you have any further questions, you can check out the documentation I found for you on the subject.
I hope this helps, and I would be honored if I could solve your problem!
DirectQuery in Power BI - Power BI | Microsoft Learn

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[johnt75]

You can do this in power query.

Work out the number of days between the start and end date.

Work out the daily amount by dividing the amount by the number of days.

Use List.Dates to build a list of all dates between start and end date.

Remove unneeded columns.

Expand the list to new rows.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJLTVIwVDAyMDKFcoxhHGOlWJ1oJSNsagwNYDwjA6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [key = _t, #"start date" = _t, #"end date" = _t, quantity = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"key", Int64.Type}, {"start date", type date}, {"end date", type date}, {"quantity", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Num days", each Duration.TotalDays([end date] - [start date]) + 1, Int64.Type),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Daily Amount", each [quantity] / [Num days]),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "AllDates", each List.Dates( [start date], [Num days], #duration(1,0,0,0))),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom2",{"key", "Daily Amount", "AllDates"}),
    #"Expanded AllDates" = Table.ExpandListColumn(#"Removed Other Columns", "AllDates"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded AllDates",{{"AllDates", type date}})
in
    #"Changed Type1"