Power Query M to Display Correct Start Date (Script Attached)

[Anonymous]

Hello Community,

Please see below scenario and provide your suggestion.

Background:

Below script split rows based on contract length,

Sample Data
Start Date	End Date	Contract Length
11/1/2019	10/30/2022	36

Example:Here contract length is 36 so script will split into 3 rows in 12 Months period.

Wrong Result  Descriptions: End Date ends on 10/30/2020 and second rows “start date” start from 11/1/2019.It is wrong.Start Date should Display 10/1/2020 not 11/1/2019

let
    Source = Table.Combine({#"Contracts",#"Contracts_1"}),
    AddedYearDates = 
Table.AddColumn(
Source, 
"Year Start", 
(fnAddYear) => List.Generate(
() => [YearDate = fnAddYear[Contract line item Start Date], Counter = 1],
each [YearDate] < fnAddYear[Contract line item End Date],
each [YearDate = Date.AddYears(fnAddYear[Contract line item Start Date],[Counter]),
Counter = [Counter] +1],    each [YearDate]), 
type {date} ),

ExpandedYearDates = Table.ExpandListColumn(AddedYearDates , "Year Start" ),
     #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Contract line item Start Date"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Year Start", "Contract line item Start Date"}}),

    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each Date.AddYears([Contract line item Start Date],1)),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type date}}),

    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Custom.1", each Date.AddDays([Custom],-2)),
    #"Added Conditional Column" = Table.AddColumn(#"Added Custom1", "Custom.2", each if [Custom.1] <= [Contract line item End Date] then [Custom.1] else [Contract line item End Date]),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Conditional Column",{"Custom", "Custom.1", "Contract line item End Date"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns1",{{"Custom.2", "Contract line item End Date"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Contract line item End Date", type date}}),
    
   
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Contract line item Start Date", "Contract line item End Date", "Contract Length"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Removed Other Columns",{{"Contract line item Start Date", "Start Date"}, {"Contract line item End Date", "End Date"}})
in
    #"Renamed Columns2"

[v-xuding-msft]

Hi @Anonymous ,

Please add a new column like below.

 #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each {1,2,3}),

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTUN9Q3MjC0VNJRMjTQNzYAcoyMgBxjM6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Start Date" = _t, #"End Date" = _t, #"Contract Length" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"End Date", type date}, {"Contract Length", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each {1,2,3}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom.1", each if [Custom]=1 then [Start Date] else Date.AddDays(Date.AddYears([Start Date],[Custom]),-1)),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each Date.AddDays(Date.AddYears([Start Date],[Custom]),-2)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Start Date", "End Date", "Custom"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Custom.1", "Custom.2", "Contract Length"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Custom.1", "Start date"}, {"Custom.2", "End date"}})
in
    #"Renamed Columns"

Best Regards,

Xue Ding

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

[Anonymous]

Hi Xue,

Thank you so much for your response.

It is working fine for contract months 36 but when it comes to diffrent contract months i am not getting right result.I did some tweakes in your script but not getting right result.

Can you please suggest some work around?

My original script generate correct result only problem is with start date.

Start Date	End Date	Contract Length
5-Aug-18	4-Mar-19	7 Months
2-Jan-19	1-Jan-20	12 Months
3-Sep-19	22-Nov-20	15 Months
14-Dec-19	12-Dec-23	48 Months
Expected Result
Start Date	End Date	Contract Length
5-Aug-18	4-Mar-19	7 Months
2-Jan-19	1-Jan-20	12 Months
3-Sep-19	1-Sep-20	15 Months
2-Sep-20	22-Nov-20	15 Months
14-Dec-19	12-Dec-20	48 Months
13-Dec-20	12-Dec-21	48 Months
13-Dec-21	12-Dec-22	48 Months
13-Dec-22	12-Dec-23	48 Months

[v-xuding-msft · Accepted solution]

Hi @Anonymous ,

Sorry for late back, please try this code :

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtV1LE3XNbRQ0lEy0fVNLNI1tAQyzRV88/NKMoqVYnWilYx0vRLzIOKGYKaRAYhphKzGWDc4tQCixshI1y+/DKrIFFmRoYmuS2oy1CQjMNvIGGSxBVxVLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Start Date" = _t, #"End Date" = _t, #"Contract Length" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"End Date", type date}, {"Contract Length", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Contract Length", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Contract Length.1", "Contract Length.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Contract Length.1", Int64.Type}, {"Contract Length.2", type text}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom", each Value.Divide([Contract Length.1],12)),
    #"Inserted Integer-Division" = Table.AddColumn(#"Added Custom1", "Integer-Division", each Number.IntegerDivide([Contract Length.1], 12), Int64.Type),
    #"Added Custom2" = Table.AddColumn(#"Inserted Integer-Division", "Custom.1", each let a= [Custom],b = [#"Integer-Division"] in 
if a<1 or a>b then List.Generate(()=>1,each _<= a+1,each _ +1) else List.Generate(()=>1,each _<= a,each _ +1)),
    #"Expanded Custom.1" = Table.ExpandListColumn(#"Added Custom2", "Custom.1"),
    #"Added Custom3" = Table.AddColumn(#"Expanded Custom.1", "Custom.3", each if [Custom.1]<>1 and [Custom]>1 and [Custom]<>[#"Integer-Division"] then [Custom]-[#"Integer-Division"] else [Custom.1]),
    #"Added Custom" = Table.AddColumn(#"Added Custom3", "Custom.2", each if [Custom.1]=1 then [Start Date] else Date.AddDays(Date.AddYears([Start Date],[Custom.1]-1),-1)),
    #"Added Custom4" = Table.AddColumn(#"Added Custom", "Custom.4", each if [Contract Length.1] <=12 
then Date.AddDays(Date.AddMonths([Start Date],[Contract Length.1]),-1) 
else 
    if [Custom.1] = [Custom.3] 
    then Date.AddDays(Date.AddYears([Start Date],[Custom.3]),-2) 
    else Date.AddMonths([Custom.2],[Custom.3]*12)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom4",{"Start Date", "End Date", "Custom", "Integer-Division", "Custom.1", "Custom.3"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom.2", "Start date"}, {"Custom.4", "End date"}}),
    #"Inserted Merged Column" = Table.AddColumn(#"Renamed Columns", "Contract Length", each Text.Combine({Text.From([Contract Length.1], "en-US"), [Contract Length.2]}, " "), type text),
    #"Removed Columns1" = Table.RemoveColumns(#"Inserted Merged Column",{"Contract Length.1", "Contract Length.2"})
in
    #"Removed Columns1"

Best Regards,

Xue Ding

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

[Anonymous]

Thank you so much @v-xuding-msft ,Unbelievable solutions.😀