March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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
Solved! Go to 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
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"
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"
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
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
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
90 | |
89 | |
85 | |
73 | |
49 |
User | Count |
---|---|
169 | |
144 | |
90 | |
70 | |
58 |