Create Column as List of Values using M

[rsbin]

Hello,

Am new to Power Query so looking for a guiding hand.

I have a table with Contract Start Date and  Contract End Date.  I have converted these dates to text values YYYYMM (but not sure if this was the right thing to do).

I have a Duration (months) that I have calculated.

Record No.  Duration  Start YearMonth  End YearMonth

12	9	202003	202011
27	12	202004	202103
97	15	201910	202012

I believe what I need next is to generate a Column with a list of values so for each Record ID I have the entire list of applicable YYYYMM values.  In the end, I am trying to calculate a monthly revenue stream for each contract no.

Any help or guidance would be very much appreciated.

Kind Regards,

[Greg_Deckler]

Not entirely certain what you are trying to ultimately accomplish. Sometimes it is good to just show your source data and what you are trying to achieve and let people get creative. However, it kind of sounds like you are dealing with date intervals (start and end dates) and need to extrapolate what is in the middle. See if these two posts help. They are DAX but both were designed to deal with such situations.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364#M147

https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Revenue-Reverse-YTD/m-p/373185#M111

[ImkeF]

Hi @rsbin ,

you can use this function to create the months between your date fields: https://www.thebiccountant.com/2017/12/11/date-datesbetween-retrieve-dates-between-2-dates-power-bi-... 

Please check out this video on how to use such a function code in Power Query: 

Reuse M code (M functions) created by others easily 

[rsbin]

Hello @ImkeF ,

After watching Curbal's video link, I copied and pasted your raw function code into a new blank query.  I invoked the function to test it and it works just fine.

As a newbie to this community, I haven't yet figured out how to share my pbix file so will try to describe best I can

I created a new query with the following syntax:

=Table.SelectColumns(Import_Recur,{"Record No.","Project Amount","Contract Start Date","Contract Expiration Date"}) and generated a small table with 9 records.  Sample as follows:

Record No.Project AmountContract Start DateContract Expiration Date

12	101520	3/1/2020	11/30/2020
27	200000	4/1/2020	3/31/2021
76	108698	11/1/2020	10/31/2025
77	185960	11/1/2020	10/31/2025
97	125000	10/1/2019	12/31/2020

I then followed with the code from the blog post that I think is what I need to use.

= Table.AddColumn(Source, "ListofDates", each Date.DatesBetween([Contract Start Date],[Contract Expiration Date],"Month"))

But I get the following error message.

Expression Error: The name 'Date.DatesBetween wasn't recognized.  Make sure it is spelled correctly.

I must be missing a step somewhere, but am stuck as to where I turn to next.  Not sure if it has to do with my Column Names or placing the function code in the right place.

Based on invoking the function, this is exactly the solution I am looking for.  I just need some guidance on how to get it to execute on my data table.

Thank you so much for your help.

[ImkeF · Accepted solution]

Hi @rsbin 

you're nearly there!

You have to make an adjustment in the red area:

= Table.AddColumn(Source, "ListofDates", each Date.DatesBetween([Contract Start Date],[Contract Expiration Date],"Month"))

That has to match the name of the query/function where you've posted my code. "." will not be allowed in query names, so it must be something else 🙂

[rsbin]

@ImkeF 

Worked!   Thank you so very much!!! 

[ImkeF]

BTW: Yesterday I've published an instruction on how to provide sample data here: https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-... 

So if you cannot make it work, just post the link to your file according to the instructions above.

[rsbin]

Thank you both for the replies.  I will study them over the weekend and let you know