Expand Dates in power query/M

[Anonymous]

Hi,

I have a table with disconnected dates below, I would like to have a table with continuous dates with the same information between the dates. Just expand the dates until a day before the date on the next row. Can someone show me how to expand the dates below in power query/M ? Thanks

[v-eqin-msft · Accepted solution]

Hi @Anonymous ,

Please try the following M syntax:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xcy5DQAhDETRXhwj4TGHKYMc0X8by2IHyMEkT6O/Fk1QIj3rGS0LC2gn52E8AoOvizwuh2qzfw2u3tHg8H9/vPxerM/u+wM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, Units = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}, {"Units", Int64.Type}, {"Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let Category_ = [Category],Date_ = [Date] in List.Min(Table.SelectRows(#"Changed Type", each [Category] = Category_ and [Date] > Date_)[Date])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "EndDate", each if [Custom] is null then Date.AddDays(DateTime.Date(DateTime.LocalNow()) ,-1) else Date.AddDays([Custom],-1)),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each { Number.From([Date])..Number.From([EndDate]) }),
    #"Expanded Custom.2" = Table.ExpandListColumn(#"Added Custom2", "Custom.2"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom.2",{{"Custom.2", type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Date", "Custom", "EndDate"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom.2", "Date"}})
in
    #"Renamed Columns"

The final output is shown below:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

My apologies, I forgot to include two more details. I actually have a category as well and I want the last row of each Category to be expanded until yesterday's date.

[Ashish_Mathur]

Have you tried my solution?

[v-eqin-msft]

Hi @Anonymous ,

Please follow these steps:

1. Add a Index column start with 0

2. Get the next date based on Index+1 

3. Add a custom column to get all date list by using  { Number.From([StartDate])..Number.From([EndDate]) }

4. Expand the date list

5. Change data type to Date
6.Remove unnecessary columns

Here is the whole M code and the final output:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlfSUTLTNzTVNzIwMlSK1YlWsoCIWCBEDA3AQkZGUKFYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Units = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Units", Int64.Type}, {"Date", type date}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "EndDate", each try  Date.AddDays(#"Added Index" [Date] { [Index]+1 } ,-1) otherwise Date.AddDays([Date],2)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each { Number.From([Date])..Number.From([EndDate]) }),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom1", "Custom"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Date", "Index", "EndDate"})
in
    #"Removed Columns"

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ryan_mayu]

@Anonymous 

maybe you can try this

1. you need to have a date table.

2. merge two tables, date table left join the fact table.

3. fill down the column

pls see the attachment beloweex

[Ashish_Mathur]

Hi,

What is the basis of splitting row 1 into 3 rows. row 2 into 4 rows?  Give a detailed explanation.

[Anonymous]

@Ashish_Mathur , I have edited my post with more details. The basis is to expand the dates until a day before the date on the next row with same information between the dates.

[Ashish_Mathur]

Hi,

This M code works

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlfSUTI01TUw0zUyMDJUitWJVrIACVmgCBkaAMWMjBBisQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Units = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Units", Int64.Type}, {"Date", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each try #"Added Index" [Date] { [Index]  + 1 } otherwise [Date]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if [Date]=[Custom] then [Custom] else Date.AddDays([Custom],-1)),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Date.1", each {Number.From([Date])..Number.From([Custom.1])}),
    #"Expanded Date.1" = Table.ExpandListColumn(#"Added Custom2", "Date.1"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Date.1",{"Date", "Index", "Custom", "Custom.1"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Date.1", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Date.1", "Date"}})
in
    #"Renamed Columns"

Hope this helps.