Dynamic Build String of Dates with the 1st Append new Month start and end date

[icassiem]

Good day,

I have an API where I need to feed the new month's start and end dates but keep the prior dates; i cant manually pre-poluate the 1 record string value as the API fails as date not found

{"2024-06-01","2024-06-30","2024-07-01","2024-07-31","2024-08-01","2024-08-31","2024-09-01","2024-09-30","2024-10-01","2024-10-31","2024-11-01","2024-11-30","2024-12-01","2024-12-31","2025-01-01","2025-01-31","2025-02-01","2025-02-28"}

Starting Jun 2024 but when on the 1-3rd of the new month, append the new month start and end date
End result: {"2024-06-01","2024-06-30","2024-07-01","2024-07-31","2024-08-01","2024-08-31","2024-09-01","2024-09-30","2024-10-01","2024-10-31","2024-11-01","2024-11-30","2024-12-01","2024-12-31","2025-01-01","2025-01-31","2025-02-01","2025-02-28" "2025-03-01","2025-03-31"}

It is within "let." Perhaps I should build the string separately, then call the function in the API call?

Please help
Regards

[v-pnaroju-msft · Accepted solution]

Hi @icassiem,

We sincerely appreciate your inquiry through the Microsoft Fabric Community Forum.

Based on my understanding of your query, please find attached the code and the relevant screenshot for your reference. 

let
Source = {"2024-06-01","2024-06-30","2024-07-01","2024-07-31","2024-08-01","2024-08-31","2024-09-01","2024-09-30","2024-10-01","2024-10-31","2024-11-01","2024-11-30","2024-12-01","2024-12-31","2025-01-01","2025-01-31","2025-02-01","2025-02-28"},
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Split Column by Delimiter" = Table.SplitColumn(#"Converted to Table", "Column1", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", Int64.Type}, {"Column1.2", Int64.Type}, {"Column1.3", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1.1", "Year"}, {"Column1.2", "Month"}, {"Column1.3", "Date"}}),
#"Added Conditional Column" = Table.AddColumn(#"Renamed Columns", "APIString", each if [Month] = 6 then "{""""2024-06-01"""",""""2024-06-30""""}" else if [Month] = 7 then "{""""2024-06-01"""",""""2024-06-30"""",""""2024-07-01"""",""""2024-07-31""""}" else if [Month] = 8 then " {""""2024-06-01"""",""""2024-06-30"""",""""2024-07-01"""",""""2024-07-31"""",""""2024-08-01"""",""""2024-08-31""""}" else if [Month] = 9 then "{""2024-06-01"",""2024-06-30"",""2024-07-01"",""2024-07-31"",""2024-08-01"",""2024-08-31"",""2024-09-01"",""2024-09-30""}" else if [Month] = 10 then "{""2024-06-01"",""2024-06-30"",""2024-07-01"",""2024-07-31"",""2024-08-01"",""2024-08-31"",""2024-09-01"",""2024-09-30"",""2024-10-01"",""2024-10-31""}" else if [Month] = 11 then "{""2024-06-01"",""2024-06-30"",""2024-07-01"",""2024-07-31"",""2024-08-01"",""2024-08-31"",""2024-09-01"",""2024-09-30"",""2024-10-01"",""2024-10-31"",""2024-11-01"",""2024-11-30""}" else if [Month] = 12 then "{""2024-06-01"",""2024-06-30"",""2024-07-01"",""2024-07-31"",""2024-08-01"",""2024-08-31"",""2024-09-01"",""2024-09-30"",""2024-10-01"",""2024-10-31"",""2024-11-01"",""2024-11-30"",""2024-12-01"",""2024-12-31""}" else if [Month] = 1 then "{""2024-06-01"",""2024-06-30"",""2024-07-01"",""2024-07-31"",""2024-08-01"",""2024-08-31"",""2024-09-01"",""2024-09-30"",""2024-10-01"",""2024-10-31"",""2024-11-01"",""2024-11-30"",""2024-12-01"",""2024-12-31"",""2025-01-01"",""2025-01-31""}" else if [Month] = 2 then "{""2024-06-01"",""2024-06-30"",""2024-07-01"",""2024-07-31"",""2024-08-01"",""2024-08-31"",""2024-09-01"",""2024-09-30"",""2024-10-01"",""2024-10-31"",""2024-11-01"",""2024-11-30"",""2024-12-01"",""2024-12-31"",""2025-01-01"",""2025-01-31"",""2025-02-01"",""2025-02-28""}" else null)
in
#"Added Conditional Column"

I hope this helps in resolving the issue.

If you find our response helpful, kindly mark it as the accepted solution and provide kudos, as this will assist other community members facing similar queries.

Thank you.

[icassiem]

Thank You @v-pnaroju-msft 

_month = Date.Month(Date.From(DateTime.LocalNow())),
_body = if _month = 6 then { "2024-06-01","2024-06-30" }
else if _month = 7 then { "2024-06-01","2024-06-30","2024-07-01","2024-07-31" }
else if _month = 8 then { "2024-06-01","2024-06-30","2024-07-01","2024-07-31","2024-08-01","2024-08-31" }
else if _month = 9 then { "2024-06-01","2024-06-30","2024-07-01","2024-07-31","2024-08-01","2024-08-31","2024-09-01","2024-09-30" }
else if _month = 10 then { "2024-06-01","2024-06-30","2024-07-01","2024-07-31","2024-08-01","2024-08-31","2024-09-01","2024-09-30","2024-10-01","2024-10-31" }
else if _month = 11 then { "2024-06-01","2024-06-30","2024-07-01","2024-07-31","2024-08-01","2024-08-31","2024-09-01","2024-09-30","2024-10-01","2024-10-31","2024-11-01","2024-11-30" }
else if _month = 12 then { "2024-06-01","2024-06-30","2024-07-01","2024-07-31","2024-08-01","2024-08-31","2024-09-01","2024-09-30","2024-10-01","2024-10-31","2024-11-01","2024-11-30","2024-12-01","2024-12-31" }
else if _month = 1 then { "2024-06-01","2024-06-30","2024-07-01","2024-07-31","2024-08-01","2024-08-31","2024-09-01","2024-09-30","2024-10-01","2024-10-31","2024-11-01","2024-11-30","2024-12-01","2024-12-31","2025-01-01","2025-01-31" }
else if _month = 2 then { "2024-06-01","2024-06-30","2024-07-01","2024-07-31","2024-08-01","2024-08-31","2024-09-01","2024-09-30","2024-10-01","2024-10-31","2024-11-01","2024-11-30","2024-12-01","2024-12-31","2025-01-01","2025-01-31","2025-02-01","2025-02-28" }
else if _month = 3 then { "2024-06-01","2024-06-30","2024-07-01","2024-07-31","2024-08-01","2024-08-31","2024-09-01","2024-09-30","2024-10-01","2024-10-31","2024-11-01","2024-11-30","2024-12-01","2024-12-31","2025-01-01","2025-01-31","2025-02-01","2025-02-28","2025-03-01","2025-03-31" }
else if _month = 4 then { "2024-06-01","2024-06-30","2024-07-01","2024-07-31","2024-08-01","2024-08-31","2024-09-01","2024-09-30","2024-10-01","2024-10-31","2024-11-01","2024-11-30","2024-12-01","2024-12-31","2025-01-01","2025-01-31","2025-02-01","2025-02-28","2025-03-01","2025-03-31","2025-04-01","2025-04-30" }
else if _month = 5 then { "2024-06-01","2024-06-30","2024-07-01","2024-07-31","2024-08-01","2024-08-31","2024-09-01","2024-09-30","2024-10-01","2024-10-31","2024-11-01","2024-11-30","2024-12-01","2024-12-31","2025-01-01","2025-01-31","2025-02-01","2025-02-28","2025-03-01","2025-03-31","2025-04-01","2025-04-30","2025-05-01","2025-05-31" }
else null,
postData = Json.FromValue([groupName = "null", dates = _body]),

[icassiem]

Hi, Please any update

Should i rather manual but some workaround

MonthNo = (Month(today())

set _dates

switch MonthNo = 7 then {"2024-06-01","2024-06-30","2024-07-01","2024-07-31"}

switch MonthNo = 8 then {"2024-06-01","2024-06-30","2024-07-01","2024-07-31", "2024-08-01","2024-08-31"}

body [dates = _dates] 

please help, im only guessing