Dynamically generate table rows for each row

[mrasenberg]

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

[fenixen]

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

[Greg_Deckler]

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.

[mrasenberg]

Hi Smoupre,

That is correct!

Regards,
Maarten

[Greg_Deckler]

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

[mrasenberg]

Thank you in advance!

[ImkeF]

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.

[mrasenberg]

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

[ImkeF · 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:

[Greg_Deckler]

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

[ImkeF]

ImkeSays := THANKX(Greg)

😉

[mrasenberg]

Thank you very much Imke!

That did the trick.

Kind regards,
Maarten