cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Advocate II
Advocate II

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

Super User IV
Super User IV

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

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

View solution in original post

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

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors