Forum Discussion
Power Query M to Display Correct Start Date (Script Attached)
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 Lengt...
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.
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.
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 |
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.
Thank you so much v-xuding-msft ,Unbelievable solutions.😀