Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have historic data for stock movements in a warehouse. Each time an SKU is produced/moved/sold, there is a log. Some items have data for most days, but others can only move once every few weeks. I've taken the last movement for each item on each day (let's call this 'item-day' data), and merged this with a blank calendar table, then filled the blank days with the values from the previous days. The problem is that if there's 10,000 items, and a calendar for each, it results in 3.65M rows. Power BI handles the rows fine, but Power Query starts to struggle with the nesting and multi-column merges. There's around 600k rows of item-day data that need merging into those 3.65M rows... That's slow. But when you go up to 5 years, that's 3M rows into 18.25M. It's too exponential to scale up.
Are there more optimised ways to fill in the data between the dates?
I've already tried to speed up the merges using JoinAlgorithm.SortMerge, but unfortunately the original SQL data is in EBCDIC sort order, rather than ASCII so I need to sort the data in PQ first anyway.
It seems weird for me to merge data into a calendar table.
I tweaked one of my previous reports to show you the fundamental pattern to deal with such a situation.
Expertise = List.Accumulate( {Days as from Today}, {Skills and Knowledge}, (Current, Everyday) => Current & Day.LeanAndPractise(Everyday) ) |
Putting it into a calendar certainly makes it easier to do a lot of the calcs. I originally made this dataset so I could see the total value of all our stocked items through the year and flick between different item groups. Makes it super easy to chart and slice in that more data warehouse setup. I'll try your method out, though I think it'll have to run 365 times over the 600k rows of non-calendar merged data. 219M processes to do on the fly, rather than 3.25M, to make a chart line like in the one below:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
105 | |
88 | |
74 | |
66 |
User | Count |
---|---|
126 | |
111 | |
100 | |
83 | |
71 |