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

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.