Desktop

[tejapowerbi123]

Happy Monday Community Members,

I have a below scenarios for your suggestions. I have used list.dates functions but it does not generate correct result because my Contract Months keep changing.

My requirements is that if contract months is >=12 Months then I want to crate  2 Rows with 12 Months Block and if contract is <12 then create one row.let me know if you need more information.Thanks

Start Date	End Date	Contract Length
1/1/2015	31/12/2015	12 Months
1/1/2015	31/07/2015	7 Months
1/1/2015	31/07/2016	19 Months
1/1/2015	31/12/2016	24 Months
1/1/2015	31/07/2018	43 Months
Start Date	End Date	Contract Length	Row Split Comments
1/1/2015	31/12/2015	12 Months	for 12 Months 2 row split
1/1/2015	31/12/2015	12 Months	for 12 Months 2 row split
1/1/2015	31/07/2015	7 Months	for 7 Months 1 Row Split
1/1/2015	31/12/2015	19 Months	for 19 Months 1 Row with 12 Months and 2 Row with 7 Months Split
1/1/2016	31/07/2016	19 Months	for 19 Months 1 Row with 12 Months and 2 Row with 7 Months Split
1/1/2015	31/12/2015	24 Months	for 24 Months 2 row split of 12 Months
1/1/2016	31/12/2016	24 Months	for 24 Months 2 row split of 12 Months
1/1/2015	31/12/2015	43 Months	for 43 Months 3 row split of 12 Months and 1 Row with 7 Months
1/1/2016	31/12/2016	43 Months	for 43 Months 3 row split of 12 Months and 1 Row with 7 Months
1/1/2017	31/12/2017	43 Months	for 43 Months 3 row split of 12 Months and 1 Row with 7 Months
1/1/2018	31/07/2018	43 Months	for 43 Months 3 row split of 12 Months and 1 Row with 7 Months

[Ashish_Mathur · Accepted solution]

Hi,

Try this code

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type datetime}, {"End Date", type datetime}, {"Contract Length", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Contract Length", "Contract Length - Copy"),
    #"Added Index" = Table.AddIndexColumn(#"Duplicated Column", "Index", 1, 1),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "Start Date", "End Date", "Contract Length", "Contract Length - Copy"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Reordered Columns", "Contract Length - Copy", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Contract Length - Copy.1", "Contract Length - Copy.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Contract Length - Copy.1", Int64.Type}, {"Contract Length - Copy.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Contract Length - Copy.2"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each if Number.Mod([#"Contract Length - Copy.1"],12) <> 0 then Number.IntegerDivide([#"Contract Length - Copy.1"], 12)+1 else if [#"Contract Length - Copy.1"]=12 then 2 else Number.IntegerDivide([#"Contract Length - Copy.1"], 12)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each {Number.From(1)..Number.From([Custom])}),
    #"Expanded Custom.1" = Table.ExpandListColumn(#"Added Custom1", "Custom.1"),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom.1",{"Custom", "Custom.1"}),
    Partition = Table.Group(#"Removed Columns1", {"Index"}, {{"Partition", each Table.AddIndexColumn(_, "Index1",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Start Date", "End Date", "Contract Length", "Contract Length - Copy.1", "Index1"}, {"Start Date", "End Date", "Contract Length", "Contract Length - Copy.1", "Index1"}),
    #"Added Custom2" = Table.AddColumn(#"Expanded Partition", "Advance start date by", each if [#"Contract Length - Copy.1"] < 12 then 0 else 12 *([Index1]-1)),

    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom", each if [#"Contract Length - Copy.1"]=12 then Date.AddMonths([Start Date],0) else Date.AddMonths([Start Date],[Advance start date by])),
    #"Reordered Columns1" = Table.ReorderColumns(#"Added Custom3",{"Index", "Start Date", "Custom", "End Date", "Contract Length", "Contract Length - Copy.1", "Index1", "Advance start date by"}),
    #"Added Custom4" = Table.AddColumn(#"Reordered Columns1", "Advance end date by", each if [#"Contract Length - Copy.1"]-[Advance start date by] > 12 then 12 else [#"Contract Length - Copy.1"]-[Advance start date by]),
    #"Added Custom5" = Table.AddColumn(#"Added Custom4", "Custom.1", each Date.AddDays(Date.AddMonths([Custom],[Advance end date by]),-1)),
    #"Reordered Columns2" = Table.ReorderColumns(#"Added Custom5",{"Index", "Start Date", "Custom", "Custom.1", "End Date", "Contract Length", "Contract Length - Copy.1", "Index1", "Advance start date by", "Advance end date by"}),
    #"Removed Columns2" = Table.RemoveColumns(#"Reordered Columns2",{"Start Date", "End Date", "Contract Length - Copy.1", "Index1", "Advance start date by", "Advance end date by"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns2",{{"Custom", "Start Date"}, {"Custom.1", "End Date"}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Renamed Columns", {{"Start Date", type date}, {"End Date", type date}}, "en-IN"),
    #"Removed Columns3" = Table.RemoveColumns(#"Changed Type with Locale",{"Index"})
in
    #"Removed Columns3"

[CR]

Hi @tejapowerbi123

I advise you to follow these steps:

• open your query editor
• duplicate your table (so we got table1 and table2)
• in the table2, duplicate the column Contract Length (Contract Length - Copy)
• in this new column Contract Length - Copy, you replace the value  Months by nothing (to keep only the numbers)
• change the format of your column (Text to Whole Number)
• select only the number granter than or equal to 12

• Remove the column Contract Length - Copy because you don't need it anymore
• Use the function Append Queries as New to create a new table made of the table1 and table2

This new table entitled Append1 is what you need if I'm not wrong.

Regards,

CR

[tejapowerbi123]

Hi CR,

Thank you so much for your suggestion.

Questions 1:What is the do with contract length has <12 ?

Question 2: How is it going to split the column by contract lengths?

Can you please explain me in details?

Thanks

[CR]

Regarding the ones <12, I just kept the original line because, in the table2, I kept only the line >= 12.

Table1: data <12 + data >=12

Table2: date >=12

Append1 = merge of Table1 + Table2 = data <12 + data >=12 + Data < 12 = 2(Data >=12) + Data <12

It refers to your requirement if contract months is >=12 Months then I want to create  2 Rows with 12 Months Block and if contract is <12 then create one row

Concerning the split you are talking about, I'mreally sorry, I just understood what you need ! Well, I'll try to reply tomorrow because you need few steps to get that (not so simple in my opinion) but probably somebody will complete.

Regards,

CR

[tejapowerbi123]

Hi CR,

thank you so much for your advice. I will wait for your response.

[Ashish_Mathur]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type datetime}, {"End Date", type datetime}, {"Contract Length", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Contract Length", "Contract Length - Copy"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Contract Length - Copy", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Contract Length - Copy.1", "Contract Length - Copy.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Contract Length - Copy.1", Int64.Type}, {"Contract Length - Copy.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Contract Length - Copy.2"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each if Number.Mod([#"Contract Length - Copy.1"],12) <> 0 then Number.IntegerDivide([#"Contract Length - Copy.1"], 12)+1 else Number.IntegerDivide([#"Contract Length - Copy.1"], 12)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each {Number.From(1)..Number.From([Custom])}),
    #"Expanded Custom.1" = Table.ExpandListColumn(#"Added Custom1", "Custom.1"),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom.1",{"Contract Length - Copy.1", "Custom", "Custom.1"})
in
    #"Removed Columns1"

[tejapowerbi123]

Hi Ashish,

Than you so much for your replay.

Still the result is not showing correct,start data and end date is not showing right

For 12 Months ,

Source without Transformation

Start Date	End Date	Contract Length
1/1/2015	31/12/2015	12 Months

Final Result

Start Date	End Date	Contract Length	Row Split Comments
1/1/2015	31/12/2015	12 Months	for 12 Months 2 row split
1/1/2015	31/12/2015	12 Months	for 12 Months 2 row split

For 19 Months,

Source without Transformation

Start Date	End Date	Contract Length
1/1/2015	31/07/2016	19 Months

Final Result

1/1/2015	31/12/2015	19 Months	for 19 Months 1 Row with 12 Months and 2 Row with 7 Months Split
1/1/2016	31/07/2016	19 Months	for 19 Months 1 Row with 12 Months and 2 Row with 7 Months Split

Let me know if you need more information.

Thanks

[tejapowerbi123]

Hello,

i have tried below code  but it did not generate correct result for 12 Months.Does anyone has any suggestions?

let
Source = Excel.CurrentWorkbook(){[Name="TableDates"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"start", type date}, {"end", type date}, {"months", Int64.Type}}),


AddedYearDates = 
Table.AddColumn(
Source, 
"Year Start", 
(fnAddYear) => List.Generate(
() => [YearDate = fnAddYear[start], Counter = 1],
each [YearDate] < fnAddYear[end],
each [YearDate = Date.AddYears(fnAddYear[start],[Counter]),
Counter = [Counter] + 1],
each [YearDate]), 
type {date} ),

ExpandedYearDates = Table.ExpandListColumn(AddedYearDates , "Year Start" ),
#"Changed Type1" = Table.TransformColumnTypes(ExpandedYearDates,{{"start", type date}, {"end", type date}, {"Year Start", type date}}),
#"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"start", "Year Start", "end", "months"}),
#"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"start"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each Date.AddYears([Year Start],1)),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type date}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type2", "Custom.1", each Date.AddDays([Custom],-1)),
#"Added Conditional Column" = Table.AddColumn(#"Added Custom1", "Custom.2", each if [Custom.1] <= [end] then [Custom.1] else [end]),
#"Removed Columns1" = Table.RemoveColumns(#"Added Conditional Column",{"Custom", "Custom.1"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Removed Columns1",{{"months", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type3",{{"Custom.2", "End"}}),
#"Changed Type4" = Table.TransformColumnTypes(#"Renamed Columns",{{"End", type date}}),
#"Reordered Columns1" = Table.ReorderColumns(#"Changed Type4",{"Year Start", "end", "End", "months"}),
Result = Table.RemoveColumns(#"Reordered Columns1",{"end"})

[Ashish_Mathur]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type datetime}, {"End Date", type datetime}, {"Contract Length", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Contract Length", "Contract Length - Copy"),
    #"Added Index" = Table.AddIndexColumn(#"Duplicated Column", "Index", 1, 1),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "Start Date", "End Date", "Contract Length", "Contract Length - Copy"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Reordered Columns", "Contract Length - Copy", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Contract Length - Copy.1", "Contract Length - Copy.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Contract Length - Copy.1", Int64.Type}, {"Contract Length - Copy.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Contract Length - Copy.2"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each if Number.Mod([#"Contract Length - Copy.1"],12) <> 0 then Number.IntegerDivide([#"Contract Length - Copy.1"], 12)+1 else if [#"Contract Length - Copy.1"]=12 then 2 else Number.IntegerDivide([#"Contract Length - Copy.1"], 12)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each {Number.From(1)..Number.From([Custom])}),
    #"Expanded Custom.1" = Table.ExpandListColumn(#"Added Custom1", "Custom.1"),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom.1",{"Custom", "Custom.1"}),
    Partition = Table.Group(#"Removed Columns1", {"Index"}, {{"Partition", each Table.AddIndexColumn(_, "Index1",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Start Date", "End Date", "Contract Length", "Contract Length - Copy.1", "Index1"}, {"Start Date", "End Date", "Contract Length", "Contract Length - Copy.1", "Index1"}),
    #"Added Custom2" = Table.AddColumn(#"Expanded Partition", "Advance start date by", each if [#"Contract Length - Copy.1"] < 12 then 0 else 12 *([Index1]-1)),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom", each Date.AddMonths([Start Date],[Advance start date by])),
    #"Reordered Columns1" = Table.ReorderColumns(#"Added Custom3",{"Index", "Start Date", "Custom", "End Date", "Contract Length", "Contract Length - Copy.1", "Index1", "Advance start date by"}),
    #"Added Custom4" = Table.AddColumn(#"Reordered Columns1", "Advance end date by", each if [#"Contract Length - Copy.1"]-[Advance start date by] > 12 then 12 else [#"Contract Length - Copy.1"]-[Advance start date by]),
    #"Added Custom5" = Table.AddColumn(#"Added Custom4", "Custom.1", each Date.AddDays(Date.AddMonths([Custom],[Advance end date by]),-1)),
    #"Reordered Columns2" = Table.ReorderColumns(#"Added Custom5",{"Index", "Start Date", "Custom", "Custom.1", "End Date", "Contract Length", "Contract Length - Copy.1", "Index1", "Advance start date by", "Advance end date by"}),
    #"Removed Columns2" = Table.RemoveColumns(#"Reordered Columns2",{"Start Date", "End Date", "Contract Length - Copy.1", "Index1", "Advance start date by", "Advance end date by"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns2",{{"Custom", "Start Date"}, {"Custom.1", "End Date"}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Renamed Columns", {{"Start Date", type date}, {"End Date", type date}}, "en-IN"),
    #"Removed Columns3" = Table.RemoveColumns(#"Changed Type with Locale",{"Index"})
in
    #"Removed Columns3"

Hope this helps.

[tejapowerbi123]

Hi Ashish,

thank you so much for your help. I am going to try it today and will you the updates.

Thanks