Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Power Query List for number of days in month

Hi All,

 

I have created the below Query after a lot of deliberations Smiley LOL


{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

 

List.PNG

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

10 REPLIES 10
Mariusz
Community Champion
Community Champion

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.
Mariusz Repczynski

 

Anonymous
Not applicable

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)

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 Repczynski

 

Mariusz
Community Champion
Community Champion

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.
Mariusz Repczynski



Anonymous
Not applicable

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 Smiley LOL some more work needed

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.
Mariusz Repczynski

 

Anonymous
Not applicable

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

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 Repczynski

 

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 Repczynski

 

Hi @Anonymous 

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

 

Best Regards,
Mariusz

Please feel free to connect with me.
Mariusz Repczynski

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.