Forum Discussion

nova_3013's avatar
nova_3013
Frequent Visitor
4 years ago
Solved

Merging Queries based on Date Ranges to get a full table

Hi   I have two tables that need to be combined. One table contains basic information about deals and another table information about the associated products.Each row consists of a state a deal was...
Need Help
  • artemus's avatar
    artemus
    4 years ago

    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.

nova_3013's avatar
nova_3013
Frequent Visitor
4 years ago

thank you artemus and Anonymous . 
In the end I resorted to create expanded tables and merge those. The performance so far seems ok.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    "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."

     

     

    "In the end I resorted to create expanded tables and merge those. The performance so far seems ok."

     

    To make the discussion of general use, could you explain which solution in the end is the one that solved the problem?
    Could you post the script you used?