cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate III
Advocate III

Expand a list of Dates until Today using functions in Power Query 'M'

Hi All,

 

I trying to transform a set of dates like the one shown in the code to a list that starts on a spüecific date and moves on until today. I need this because I need to expand the records and automatically have the dates. Otherwise, the expansion is not considering new dates. 

Table.ExpandRecordColumn(
#"Converted to Table", "Value", 
{"2017-11-01", "2017-11-02", "2017-11-03", "2017-11-04", "2017-11-05", "2017-11-06", "2017-10-02", "2017-11-08", "2017-11-09", "2017-10-14", "2017-10-15", "2017-10-16", "2017-10-17", "2017-10-10", "2017-10-11", "2017-10-12", "2017-10-13", "2017-10-18", "2017-10-19", "2017-11-19", "2017-11-18", "2017-11-17", "2017-11-16", "2017-11-15", "2017-11-14", "2017-11-13"})

I tried to create a list like this:

{Number.From(Date.FromText("2017-11-01"))..Number.From(Date.FromText("2017-12-08"))}

But I need them to be Text values so I tried to convert the list to text, also doesn't work. 

Does anyone have an idea how to do it? or if there any other way to get the data from the records directly into a table without the expand? 

Best,
J.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

@Jmenas

You can try

let 
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlSK1YlWSlKKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Record.FromTable(Table.FromRows({{"2017-11-01",1} , {"2017-11-02", 2}},{"Name", "Value"}) )),
    #"Converted to Table"= Table.RenameColumns(#"Added Custom",{{"Custom", "value"}}),

    list = List.Dates(#date(2017, 11, 1), Duration.Days(Duration.From(DateTime.Date(DateTime.LocalNow())-(#date(2017, 11, 1)))), #duration(1, 0, 0, 0)), 
    ListTyped = List.Transform(list , each Date.ToText(_,"yyyy-MM-dd") ),
    lastResult = Table.ExpandRecordColumn(#"Converted to Table", "value",ListTyped  )
in
    lastResult

View solution in original post

3 REPLIES 3
Highlighted
Microsoft
Microsoft

@Jmenas

You can try

let 
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlSK1YlWSlKKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Record.FromTable(Table.FromRows({{"2017-11-01",1} , {"2017-11-02", 2}},{"Name", "Value"}) )),
    #"Converted to Table"= Table.RenameColumns(#"Added Custom",{{"Custom", "value"}}),

    list = List.Dates(#date(2017, 11, 1), Duration.Days(Duration.From(DateTime.Date(DateTime.LocalNow())-(#date(2017, 11, 1)))), #duration(1, 0, 0, 0)), 
    ListTyped = List.Transform(list , each Date.ToText(_,"yyyy-MM-dd") ),
    lastResult = Table.ExpandRecordColumn(#"Converted to Table", "value",ListTyped  )
in
    lastResult

View solution in original post

Hi @Eric_Zhang 

that work perfect! thanks a lot! 

Highlighted

Glad to help. 🙂

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors