Forum Discussion

TM_Visual's avatar
TM_Visual
Advocate III
7 years ago
Solved

Generating a table with values for all dates between two dates

I've got data that relates to the amount of money given to research grants, which each have a start and end date.   I have a table that looks like this: ResearchGrant Award total Award start ...
Need Help
  • jthomson's avatar
    jthomson
    7 years ago

    I need to do something similar in a report I have. In Power Query, transform the overall grant amount into a daily amount first, then use this sort of formula:

     

    = Table.AddColumn(#"Some Previous Step Name", "Date", each List.Dates([Award Start], Duration.TotalDays([Award End]-[Award Start])+1, #duration(1,0,0,0)))

     

    Then expand the list

jthomson's avatar
jthomson
Solution Sage
7 years ago

I need to do something similar in a report I have. In Power Query, transform the overall grant amount into a daily amount first, then use this sort of formula:

 

= Table.AddColumn(#"Some Previous Step Name", "Date", each List.Dates([Award Start], Duration.TotalDays([Award End]-[Award Start])+1, #duration(1,0,0,0)))

 

Then expand the list

TM_Visual's avatar
TM_Visual
Advocate III
7 years ago

Thank you jthomson , this is the method I used to resolve this.

 

For the reference of future users who find this post, I had a few errors popping up when I tried to do this. The error message text wasn't always clear, so I suggest (in order):

  • checking that you have no invalid dates in your start or end date columns
  • checking that no end dates were earlier than start dates
  • running the change in a new table duplicate of your original, one that doesn't have existing relationships to other tables. Power BI stopped me making changes because my 'Award ID' was previously the unique key for the table
  • checking that you haven't messed up your date table somehow