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.
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.
Thank you very much for your suggestion. That was actually what I wanted to do at first. However, this is obviously just a small example and this table will grow huge as I have thousands of deals and products. That is why I wanted to look for another solution and try to avoid this.
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.
