Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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"
Solved! Go to 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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
102 | |
84 | |
79 | |
70 |
User | Count |
---|---|
120 | |
110 | |
95 | |
82 | |
77 |