Power Query List for number of days in month

[Anonymous]

Hi All,

I have created the below Query after a lot of deliberations

{Number.From(Date.FromText(Path_Year&"-"&Path_Month&"-01"))..Number.From(Date.EndOfMonth(Date.FromText(Path_Year&"-"&Path_Month&"-01")))}

Parameters used:

Path_Year is set to Text and = 2019

Path_Month is set to Text and is a List = 01 to 12

Path_Day is what I am trying to populate from the above query

So if we look at above query it generates below list for July. What I would actually like is for it to say 01-31.

I'll basically be using it for Folder Path in Source for another folder import query

[Mariusz]

Hi @Anonymous 

You can use List.Dates instead of {..}

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[Anonymous]

Hi @Mariusz 

Sorry I wasn't clear. I don't need the entire dates, just the day numbers.

Essentially folder path is like  \\Server\FolderName\2019\07\26

So Im trying to convert that to be just the day number from 01 to 31 (or less depending on the month range as per query)

[Mariusz]

Hi @Anonymous 

You can create a add a custom like in the example below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtFTSUTIwVIrVQfCMUHjGKDwTFJ4pCs8MhWeOwrPAzTM0QOGhuMUQ6JZYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Year = _t, Month = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each let _start = Date.FromText( [Year] & "-" & [Month] & "-01" ) in List.Transform( List.Dates( _start, Duration.Days( Date.EndOfMonth( _start ) - _start ), #duration( 1, 0, 0, 0 ) ), each Date.Day( _ ) )),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")
in
    #"Expanded Custom"

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[Mariusz]

Hi @Anonymous 

Small M script correction that converts 1 - 31 to 01 - 31.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtFTSUTIwVIrVQfCMUHjGKDwTFJ4pCs8MhWeOwrPAzTM0QOGhuMUQ6JZYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Year = _t, Month = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each let _start = Date.FromText( [Year] & "-" & [Month] & "-01" ) in List.Transform( List.Dates( _start, Duration.Days( Date.EndOfMonth( _start ) - _start ), #duration( 1, 0, 0, 0 ) ), each Text.PadStart( Text.From( Date.Day( _ ) ), 2, "0") )),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")
in
    #"Expanded Custom"

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[Anonymous]

Hi @Mariusz 

Thanks looks super complicated, any chance I could get an explanation. Also I created a simpler version by modifying my query to below so I can continue to use my parameters

 {Date.Day(Date.FromText(Path_Year&"-"&Path_Month&"-01"))..Date.Day(Date.EndOfMonth(Date.FromText(Path_Year&"-"&Path_Month&"-01")))}

but my code doesn't do 0 at the front some more work needed

[Mariusz]

Hi @Anonymous 

I've converted the code in to a function this should be easier to understand and see the flow.

(_year as number, _month as number ) => let
    startDate = #date( _year, _month, 1 ),
    endDate = Date.EndOfMonth( startDate ),
    listOfDates = List.Dates( startDate, Duration.Days( endDate - startDate ), #duration( 1, 0, 0, 0 ) ),
    transformDates = List.Transform( listOfDates , each Text.PadStart( Text.From( Date.Day( _ ) ), 2, "0") )
in
    transformDates

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[Anonymous · Accepted solution]

Hi @Mariusz 

Basically I have no clue how the JSON/ Binary part works. I understood the let _t part cos that looks relatively normal.

Also managed to add the 0 on the front thorugh trial & error and GOOGLE lol

let
    Source = {Date.Day(Date.FromText(Path_Year&"-"&Path_Month&"-01"))..Date.Day(Date.EndOfMonth(Date.FromText(Path_Year&"-"&Path_Month&"-01")))},
    Custom1 = List.Transform(Source, each Text.PadStart(Number.ToText(_),2,"0"))
in
    Custom1

[Mariusz]

Hi @Anonymous 

I'm glad you figured it out!
JSON/ Binary part is created when you use "Enter Data" in query editor.

Best Regards,
Mariusz

Please feel free to connect with me.

[Mariusz]

HI @Anonymous 

The part of the code that transforms to 01 is Text.PadStart( Text.From( Date.Day( _ ) ), 2, "0") )

Hope this helps!

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[Mariusz]

Hi @Anonymous 

No problem, what part would you like me to explain?

Best Regards,
Mariusz

Please feel free to connect with me.