Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi,
I have a Power Query table that contains a list of dates, however these dates are not consecutive. Take a look at the column in question, below:
I need to make to make the column's list of dates includes every single dates between the column's MIN and MAX dates. For example, the column MIN date is 01/08/2008 (as you can see in the above screenshot). The MAX date is 03/08/2024. I need the column list to be every date between those two dates.
How do I achieve this? It has to be done in Power Query.
Thanks.
Solved! Go to Solution.
@HotChilli thanks for the quick response. I came across the post you linked a little earlier and my attempt at is it below, but it's erroring. I'm clearly misunderstanding something. [Date] is the date column within the table in question.
=Table.AddColumn( #"Removed Duplicates", "Date List", each List.Dates( List.Min( [Date] ), Duration.Days( List.Max( [Date] ) - List.Min( [Date] ) ), 1,0,0,0))
EDIT: I've just found a video that helped me achieve my aim. I just needed that extra visual on it @HotChilli, but thanks the same. The video's link is below in case it's helpful for others.
https://www.youtube.com/watch?v=Ase0yNEHApI
@HotChilli thanks for the quick response. I came across the post you linked a little earlier and my attempt at is it below, but it's erroring. I'm clearly misunderstanding something. [Date] is the date column within the table in question.
=Table.AddColumn( #"Removed Duplicates", "Date List", each List.Dates( List.Min( [Date] ), Duration.Days( List.Max( [Date] ) - List.Min( [Date] ) ), 1,0,0,0))
EDIT: I've just found a video that helped me achieve my aim. I just needed that extra visual on it @HotChilli, but thanks the same. The video's link is below in case it's helpful for others.
https://www.youtube.com/watch?v=Ase0yNEHApI
Why does it have to be done in Power Query? If using Power BI you can just create a date reference table with every date forever and use that as your date reference. Link that to your current date and use the "all dates" table as your filter date reference so you can select any possible date and if that date does not appear in your data then it will just show no data for that day.
You can do this sort of thing
or you can create a list from min date to max date and then expand it:
{Number.From([MinDate])..Number.From([MaxDate]) }
-> you need to get the mindate and maxdate but I'll leave that for you to do
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
105 | |
99 | |
99 | |
38 | |
37 |
User | Count |
---|---|
156 | |
121 | |
73 | |
73 | |
63 |