Forum Discussion
Merging Queries based on Date Ranges to get a full table
I would expand these tables to have 1 deal/product row per day.
You can do this with by adding a custom column that lists all the dates between the start and end period.
= List.Generate(() => [Start Date], each _ <= [End Date], each _ + #duration(1, 0, 0, 0))
After doing that just click the new column and choose expand list value. From this point you should be able to merge the tables, and add some logic for the Product attributes.
If you going to create some kind of interactive power bi report with this, I would leave the data in this format. If your loading it for quick viewing, you will need to do a grouping operator with the min and max of the date column to get back the starting and ending date.
could you give the dimensions of your tables in terms of rows and columns?
the solution I proposed does not use list.generate and could be sufficiently performing.
Otherwise you should look for other tools.
DataFrames.jl in the julia data ecosystem, for example.
These two tables each have about 8 columns and approximately 3000 rows. With all the timestamps and changes I already get to more than 300'000 rows each for these two tables.
I'm a bit afraid that the data will grow really fast and will soon reach 1+ millions of rows so that performance could be an issue.
give a try, starting from a subset of db (f.i from 500 or 1k rows) and let's know the execution time.
Could you provide a full db (CSV or XLSX) with tokenized sensible data?