Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi all,
Being quite new to Power Bi, I'm reaching out to this community.
What I'm trying to achieve is to dynamically generate table rows for each date in a date range.
The source:
Id StartDate EndDate DateDifference 7069 01-11-2016 04-11-2016 3 7070 01-11-2016 04-11-2016 3 7071 31-10-2016 04-11-2016 4 7072 06-11-2016 11-11-2016 5
The result should be a row for each date between the StartDate and EndDate.
I was looking for a Power Query solution but that didn't worked out (by me).
Anyone a hint or solution?
Thanks in advance!
Kind regards,
Maarten Rasenberg
Solved! Go to Solution.
By expanding the list-column that has been generated : The last column "Custom" has been created by the formula -> you see the list and expand it with the double-arrow in the headers:
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
This topic really saved my day, critical customer need got solved "on the hour", thanks! 🙂
So, would your expected result be:
01-11-2016 7069 02-11-2016 7069 03-11-2016 7069 04-11-2016 7069 01-11-2016 7070 02-11-2016 7070 03-11-2016 7070 04-11-2016 7070 31-10-2016 7071 01-11-2016 7071 02-11-2016 7071 03-11-2016 7071 04-11-2016 7071 06-11-2016 7072 07-11-2016 7072 08-11-2016 7072 09-11-2016 7072 10-11-2016 7072 11-11-2016 7072
If not, please provide what you are looking to achieve.
Hi Smoupre,
That is correct!
Regards,
Maarten
OK, well that was the easy part, now I'll have to try to figure out how to accomplish it!
Thank you in advance!
If I may step in here - there is a function in M for it: List.Dates, which you put into a CustomColumn like this:
List.Dates([StartDate], [DateDifference]+1, #duration(1,0,0,0))
This produces a list that you can then expand.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi Imke,
I saw that function in my search to the solution also. It is working fine, but how am I able to generate new records for each table row?
Maarten Rasenberg
By expanding the list-column that has been generated : The last column "Custom" has been created by the formula -> you see the list and expand it with the double-arrow in the headers:
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Thanks @ImkeF, I hope you know that you are ALWAYS welcome to step in and provide the answer!! 🙂 If I had figured out that it was an "M" problem I would most likely have invoked you anyway but I got pulled away and just got back to this thread now. I had pretty much determined that it wasn't going to be possible to do this in DAX but that's as far as I got!
function invokeImkeF(q) { a = magic(q); return a; } invokeImkeF("Dynamically generate table rows for each row");
It's just psuedo-code right now...
ImkeSays := THANKX(Greg)
😉
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Thank you very much Imke!
That did the trick.
Kind regards,
Maarten
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
87 | |
85 | |
67 | |
49 |
User | Count |
---|---|
135 | |
112 | |
100 | |
66 | |
62 |