Cumulative for missing weeks

[mafaber]

Hi,

I have a table containing demand per product in weekly buckets, I would like to calculate a cumulated demand for all weeks starting week00 to week53 (I have a scope of 1 year).

My issue is if there is no demand for a product on a certain week, when I do the cumulative calculation in Power Query, I only see cumulatives for weeks where there is demand. For product-week combinations that originally didn't exist in the table, because there was no demand on that week, cumulatives are missing (resulting in 0 when visualizing it).

I would like to see cumulatives for all weeks, what is the best practice to add the missing rows?

Data and calculations are quite heavy in the end, so it would be best avoiding mergeing and similar resource heavy calculations if possible.

As you can see, cumulative calc is ok, but week01, wk03 and wk04 are missing.

[ppm1]

You can create a second query that lists all the week numbers and then merge your original query into that on the weeknumber column. You would then see all weeks (but have null values on the missing ones).

To get started, create a blank query with this as the first step. Then convert that to a table and merge in your existing query.

= List.Transform({1..53}, each "wk" & Text.PadStart(Text.From(_),2,"0"))

Pat

[mafaber]

I made it work. I had to create a supplementary table with all the existing products (remove other columns, remove duplicates), then added a calculated column with a list of weeks ({wk00, wk01, ..., wk52}) then expand it, so I had 53 rows per product, then I appended it to the original table, so all product-week combinations exist.

It's kind of unecessary amount of data and calculations, so if anyone has any better ideas, please share, but it works.

Thank you for the tip.

[mafaber]

I tried this way, but the issue I got stuck with then is that I got all columns blank, not just the value. Since I have multiple products and the missing week could be first or last, I couldn't simply fill up or down.

How do you deal with this?

Thank you!