M code Date.StartOfMonth where should is go?

[dicken]

Hi,

I have a lsit of a list of dates produced by uisng ;

Table.AddColumn(#"Changed Type", "Custom", each 
List.Transform(
 { Number.From( [#"Sart Date "])..Number.From([End Date] )}, (_)=> Date.From(_) ) )

the output of which is :
but I need the dates is the lists to be the start of the moth, but can't get it right as to where Date.StartOfMonth should be placed , I just keep getting errors.

Richard

 

[JGordon11]

Here's an example you can adjust to fit:

let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    tbl = Table.TransformColumnTypes(Source,{{"Start", type date}, {"End", type date}}),
    Result = Table.AddColumn(tbl, "Custom", each List.Generate(()=> Date.StartOfMonth([Start]), (x)=> x<=[End], (x)=> Date.AddMonths(x ,1))),
    ShowList = Table.TransformColumns(Result, {{"Custom", each Text.Combine(List.Transform(_, Text.From),", ")}})
in
    ShowList

PQ example.xlsx
	A	B	C	D
1	Start	End
2	1/1/2022	5/21/2022
3	2/3/2021	3/4/2022
4	3/5/2022	3/23/2022
5
6	Start	End	Custom
7	1/1/2022	5/21/2022	1/1/2022, 2/1/2022, 3/1/2022, 4/1/2022, 5/1/2022
8	2/3/2021	3/4/2022	2/1/2021, 3/1/2021, 4/1/2021, 5/1/2021, 6/1/2021, 7/1/2021, 8/1/2021, 9/1/2021, 10/1/2021, 11/1/2021, 12/1/2021, 1/1/2022, 2/1/2022, 3/1/2022
9	3/5/2022	3/23/2022	3/1/2022
10
Sheet4

 

[dicken]

Here's an example you can adjust to fit:

let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    tbl = Table.TransformColumnTypes(Source,{{"Start", type date}, {"End", type date}}),
    Result = Table.AddColumn(tbl, "Custom", each List.Generate(()=> Date.StartOfMonth([Start]), (x)=> x<=[End], (x)=> Date.AddMonths(x ,1))),
    ShowList = Table.TransformColumns(Result, {{"Custom", each Text.Combine(List.Transform(_, Text.From),", ")}})
in
    ShowList

PQ example.xlsx
	A	B	C	D
1	Start	End
2	1/1/2022	5/21/2022
3	2/3/2021	3/4/2022
4	3/5/2022	3/23/2022
5
6	Start	End	Custom
7	1/1/2022	5/21/2022	1/1/2022, 2/1/2022, 3/1/2022, 4/1/2022, 5/1/2022
8	2/3/2021	3/4/2022	2/1/2021, 3/1/2021, 4/1/2021, 5/1/2021, 6/1/2021, 7/1/2021, 8/1/2021, 9/1/2021, 10/1/2021, 11/1/2021, 12/1/2021, 1/1/2022, 2/1/2022, 3/1/2022
9	3/5/2022	3/23/2022	3/1/2022
10
Sheet4

Thanks,

Richard.