Forum Discussion
Create Column as List of Values using M
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 🙂
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
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-power-query/
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
Thank you both for the replies. I will study them over the weekend and let you know
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.
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 🙂
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-p/963216
So if you cannot make it work, just post the link to your file according to the instructions above.
