Forum Discussion
Split per month recurrent values
Hi MigCua ,
Add a new custom column in Power Query like this:
List.Distinct( List.Transform( {Number.From([Date valid from])..Number.From([Date valid to])}, each Date.StartOfMonth(Date.From(_)) ) )This should give you a nested list of the first of each month on each row.
Expand this list to new rows and you'll have everything you nee to report as required.
Pete
Hi MigCua ,
Add a new custom column in Power Query like this:
List.Distinct(
List.Transform(
{Number.From([Date valid from])..Number.From([Date valid to])},
each Date.StartOfMonth(Date.From(_))
)
)
This should give you a nested list of the first of each month on each row.
Expand this list to new rows and you'll have everything you nee to report as required.
Pete
Hi Pete, thanks for your fast response!
This column should be created within the same table as the data is located right?
I have tried to used the script you are stating but I am getting error, is there anything i need to change from the end of the clause?Thanks again!!
Hi MigCua ,
Difficult to say what needs to be done without more info.
What error do you get when you try and use this code?
If you can share some anonymised sample data in a copyable format here (paste table from Excel, or provide M code from an 'Enter Data' query) I can create the steps for you.
Pete
