Create multiple lines based on 1 row's start & end date

E

ExcelQuestion

New member
Joined
May 27, 2018
Messages
26
Reaction score
0
Points
0
Excel Version(s)
Office 365
Hello Experts,

In Power Query, how do you create a stacked data set based on 1 row's data of Description | Start Date | End Date | Amount?

From:
Electricity | January 2018 | April 2018 | $100

To:
Electricity | January 2018 | $100
Electricity | February 2018 | $100
Electricity | March 2018 | $100
Electricity | April 2018 | $100

Thanks in advance,
Ricky
 
Hi Ricky,

Select the columns with electricity and the $100 amount (hold down Ctenophora to select multiple columns). Then right click the header of one of the selected columns , unpivot other columns

That should be it.


Sent from my iPhone using Tapatalk
 
Hi Ken,
Thanks for your help.

It didn't quite work.

Result:
Electricity | Start Date | January 2018 | Amount
Electricity | End Date | April 2018 | Amount

How do you add the logic so that a new "Date" column would populate an incremental month within the date range?

Trying to achieve:
Electricity | January 2018 | $100
Electricity | February 2018 | $100 <<< populate a date that is between the date range
Electricity | March 2018 | $100 <<< populate a date that is between the date range
Electricity | April 2018 | $100

Hence, the number of rows would be dependent on the number of months between the Start Date and the End Date.

Thanks again for your help. I appreciate it very much.

Regards,
Ricky
 
Ah, my bad, I misread that. Yes, that's trickier.

Honestly, I'd build a full calendar table, then use a custom function to filter the calendar table down to just the values I need for each row. (Sample attached)

The key bit here, to make this perform well, is to buffer the calendar table at the beginning of the Output query before passing it into the function. If you don't do that, it will rebuild the calendar in full for each row of the Output table.
 

Attachments

  • Ricky.xlsx
    20.6 KB · Views: 29
Genius, Ken!

That is perfect. Thank-you very much!

To better my Power Query knowledge...
1)a) function "fxGetDates" <<< does the "firstdate" and "lastdate" have to be manually entered?
What's the purpose of the [Invoke] button if I do not need to manually enter the "firstdate" and "lastdate" fields?

1)b) the date format currently indicates "m/dd/yy" <<< in case the user's PC's default date format was set as "dd/m/yy" instead, would this function still work?

2) "Output" query, "Added Custom" applied step <<< is this only instance through out the Power Query connections that this "fxGetDates" function is used?
Code: 
= Table.AddColumn(RawData, "Dates", each fxGetDates([Start],[End]))


Ken, you've helped me tremendously! Thank-you again.

Regards,
Ricky


Ken Puls said:
Ah, my bad, I misread that. Yes, that's trickier.

Honestly, I'd build a full calendar table, then use a custom function to filter the calendar table down to just the values I need for each row. (Sample attached)

The key bit here, to make this perform well, is to buffer the calendar table at the beginning of the Output query before passing it into the function. If you don't do that, it will rebuild the calendar in full for each row of the Output table.
Click to expand...
 
Hi
Please do not post a question in the thread of another member or ask a different question in one of your old threads -- start a new thread. (I did it for you this time)

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

Thank you
 
Thank-you Pecoflyer for creating a new thread for me. You're right. I'll post new threads with the link to an old thread, if applicable, in the future.

Have a great 2019!

Regards,
Ricky

Pecoflyer said:
Hi
Please do not post a question in the thread of another member or ask a different question in one of your old threads -- start a new thread. (I did it for you this time)

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

Thank you
Click to expand...
 
You must log in or register to reply here.
Back
Top