Forum Discussion
Cumulative for missing weeks
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
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.
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!
