cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mrasenberg Frequent Visitor
Frequent 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

Accepted Solutions
Super User
Super User

Re: Dynamically generate table rows for each row

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

If you liked my solution, please give it a thumbs up. An if I did answer your question, please mark my post as a solution. Thanks!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

11 REPLIES 11
Super User
Super User

Re: Dynamically generate table rows for each row

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.


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

mrasenberg Frequent Visitor
Frequent Visitor

Re: Dynamically generate table rows for each row

Hi Smoupre,

 

That is correct!

 

Regards,
Maarten

Super User
Super User

Re: Dynamically generate table rows for each row

OK, well that was the easy part, now I'll have to try to figure out how to accomplish it!


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

mrasenberg Frequent Visitor
Frequent Visitor

Re: Dynamically generate table rows for each row

Thank you in advance!

Super User
Super User

Re: Dynamically generate table rows for each row

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.

 

If you liked my solution, please give it a thumbs up. An if I did answer your question, please mark my post as a solution. Thanks!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




mrasenberg Frequent Visitor
Frequent Visitor

Re: Dynamically generate table rows for each row

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

Super User
Super User

Re: Dynamically generate table rows for each row

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

If you liked my solution, please give it a thumbs up. An if I did answer your question, please mark my post as a solution. Thanks!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

mrasenberg Frequent Visitor
Frequent Visitor

Re: Dynamically generate table rows for each row

Thank you very much Imke!

 

That did the trick.

 

Kind regards,
Maarten

Super User
Super User

Re: Dynamically generate table rows for each row

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


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)