Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mrasenberg
Regular Visitor

Dynamically generate table rows for each row

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

 

1 ACCEPTED 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:

 

PBI_DynamicallyCreateRows.png

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

View solution in original post

11 REPLIES 11
fenixen
Advocate II
Advocate II

This topic really saved my day, critical customer need got solved "on the hour", thanks! 🙂 

Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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!


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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:

 

PBI_DynamicallyCreateRows.png

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.