Start date & end date in custom column by day

[Anonymous]

Hi everyone,

I have a column of agreements like this (table 1):

And should to recieve the table 2 from table 1:

And in a final, I have to get the table like this one (table 3) from table 2:

Table 1 test data:

name_client	date_start	date_end	rate	agreement
mr_x	01.10.2017	30.09.2019	3200	1
mr_x	01.12.2017	30.09.2019	3400	1
mr_x	01.01.2018	30.09.2019	3400	1
mr_x	01.12.2018	30.09.2019	3400	1
mr_x	01.01.2019	30.09.2019	3600	1
mrs_y	29.03.2019	30.09.2019	3600	2
mrs_y	01.10.2019	30.09.2024	3600	2

Does anyone have an idea how to move forward from here? To creacte Table 2 and Table 3
Many thanks,

Konstantin

[ziying35]

Hi, @Anonymous 

let
    Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("tdLBCsIwDIDhd8m5lDQdavcqIqVsQQTbQ9eDInt3M8c8CGoZDHr6A/kI9PiAFCL77nrhVKCFmP0NFPShsB9KyFNDow1qQrNfJpx66RY1uqk76Vk6tJYQFYRzZo6vhWZUtQRVEs1qQp6sOmxJzFdsSsxXuP/EroYY/P3TIKfRrjSo0nj/qS8GNb+M0xM=",BinaryEncoding.Base64),Compression.Deflate))),
    chtype = Table.TransformColumnTypes(Source,{{"date_start", type date}, {"date_end", type date}},"fr"),
    n = Table.RowCount(chtype),
    rows = List.Buffer(Table.ToRows(chtype)),
    gen = List.Generate(
              ()=>[i=0, r=rows{i}],
              each [i]<=n, 
              each [i=[i]+1, 
                    r=if rows{i}?{1}?=null then rows{[i]} 
                      else List.ReplaceRange(rows{[i]}, 2, 1, {Date.AddDays(rows{i}{1}, -1)})
                   ],
              each [r]
              ),
    tbl = Table.FromRows(List.Skip(gen),Table.ColumnNames(chtype)),
    cmbcols = Table.CombineColumns(tbl,{"date_start", "date_end"},each Expression.Evaluate(Text.Format("{#{0}..#{1}}",List.Transform(_, Number.From))),"date_custom"),
    expd = Table.ExpandListColumn(cmbcols, "date_custom"),
    chtype2 = Table.TransformColumnTypes(expd,{{"date_custom", type date}})
in
    chtype2

I only use the start date of the next row for the end-date conversion logic, the other columns are not involved in the end-date conversion logic, but it seems I'm using this logic to get the result you expected, not sure if this is right or not?

[v-juanli-msft]

Hi @Anonymous 

Use the data from your another thread,

merge query in Table1, then expand "date_start" and "date_end",

add a custom column, then expand to new rows,

finally,remove and rename columns.

Table1

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdBRCoAwCIDhu/g8RN2oPMsYnaCXeqnb5xhE0oiBID98+GDOsO3rCQGIkQmFeLaIhKQ1tIYQ2WIowWnp69TVNgaWQd1uj+p2Wz96cvpYLytRpPjHxfHnK28uyfFyAw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [name_client = _t, date_start = _t, date_end = _t, rate = _t, agreement = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"name_client", type text}, {"date_start", type text}, {"date_end", type text}, {"rate", Int64.Type}, {"agreement", Int64.Type}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "date_start", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"date_start.1", "date_start.2", "date_start.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"date_start.1", Int64.Type}, {"date_start.2", Int64.Type}, {"date_start.3", Int64.Type}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type1", {{"date_start.3", type text}, {"date_start.2", type text}, {"date_start.1", type text}}, "en-US"),{"date_start.3", "date_start.2", "date_start.1"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"date_start"),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Merged Columns", "date_end", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"date_end.1", "date_end.2", "date_end.3"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"date_end.1", Int64.Type}, {"date_end.2", Int64.Type}, {"date_end.3", Int64.Type}}),
    #"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type2", {{"date_end.3", type text}, {"date_end.2", type text}, {"date_end.1", type text}}, "en-US"),{"date_end.3", "date_end.2", "date_end.1"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"date_end"),
    #"Changed Type3" = Table.TransformColumnTypes(#"Merged Columns1",{{"date_start", type date}, {"date_end", type date}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type3", {"name_client", "agreement", "date_start"}, #"Table 2", {"name_client", "agreement", "date_start"}, "Table 2", JoinKind.LeftOuter),
    #"Expanded Table 2" = Table.ExpandTableColumn(#"Merged Queries", "Table 2", {"date_start", "date_end"}, {"Table 2.date_start", "Table 2.date_end"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Table 2",{{"Table 2.date_start", "date_start2"}, {"Table 2.date_end", "date_end2"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each List.Dates([date_start2],Duration.Days([date_end2]-[date_start2])+1,#duration(1,0,0,0))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"date_start", "date_end", "date_start2", "date_end2"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"Custom", "date_Custom"}})
in
    #"Renamed Columns1"

Table2

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdBBCoAgEIXhu7iW4c0opWcR6QRtalO3z5LEiWo1/PDxFpOSmZdpM9aAiUECHks4EHMLAcphk63S0oAK/6pxzYV+O3zrOhf67R9dt2MJCQR3hxuUXqf9FLEXIMQHF8XbV3ouXvF8AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [name_client = _t, date_start = _t, date_end = _t, rate = _t, agreement = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"name_client", type text}, {"date_start", type text}, {"date_end", type text}, {"rate", Int64.Type}, {"agreement", Int64.Type}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "date_start", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"date_start.1", "date_start.2", "date_start.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"date_start.1", Int64.Type}, {"date_start.2", Int64.Type}, {"date_start.3", Int64.Type}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type1", {{"date_start.3", type text}, {"date_start.2", type text}, {"date_start.1", type text}}, "en-US"),{"date_start.3", "date_start.2", "date_start.1"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"date_start"),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Merged Columns", "date_end", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"date_end.1", "date_end.2", "date_end.3"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"date_end.1", Int64.Type}, {"date_end.2", Int64.Type}, {"date_end.3", Int64.Type}}),
    #"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type2", {{"date_end.3", type text}, {"date_end.2", type text}, {"date_end.1", type text}}, "en-US"),{"date_end.3", "date_end.2", "date_end.1"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"date_end"),
    #"Changed Type3" = Table.TransformColumnTypes(#"Merged Columns1",{{"date_start", type date}, {"date_end", type date}})
in
    #"Changed Type3"

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-juanli-msft 

many thanks for your kind attention!

I dont have the Table 2. I need to composite it from original table (Table 1):

name_client	date_start	date_end	rate	agreement
mr_x	01.10.2017	30.09.2019	3200	1
mr_x	01.12.2017	30.09.2019	3400	1
mr_x	01.01.2018	30.09.2019	3400	1
mr_x	01.12.2018	30.09.2019	3400	1
mr_x	01.01.2019	30.09.2019	3600	1
mrs_y	29.03.2019	30.09.2019	3600	2
mrs_y	01.10.2019	30.09.2024	3600	2

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

To get Table2

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wyi2Kr1DSUTI00DfUNzIwNAeyLfWNDUBsSyDb2MjAACStFKuDUGuEQ60JFrUQpRbEKDUiQS1EqSW6UjMUpcXxlSBBfSNLPIqNUBTDwgFJsZEJiuJYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [name_client = _t, date_start = _t, date_end = _t, rate = _t, agreement = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"name_client", type text}, {"date_start", type date}, {"date_end", type date}, {"rate", Int64.Type}, {"agreement", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"name_client", Order.Ascending}, {"agreement", Order.Ascending}, {"date_start", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each try #"Added Index"[date_start]{[Index]+1} otherwise null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if [Custom]=null then [date_end] else Date.AddDays([Custom],-1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"date_end", "Index", "Custom"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom.1", "date_end"}})
in
    #"Renamed Columns"

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Many thanks, Maggie for your kind attention!
thanks , guys!

[edhans]

I'm sure we can help, but a few things:

1. we need data. Cannot use images and I'm not typing that in. See links below on how to provide data.
2. Explain your output. For example, why did the end date of agreement 1 for Mr. X change from Sept 30, 2019 to Nov 30, 2017?

Tell us how logically you are getting from table 1 to table 2 to table 3.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

[Anonymous]

Hi @edhans 

1. Added in the end of 1 message.

2. Let`s move from the top of Table 1 by rows:

• 1.  The agreement was cocluded from 01.10.2017 till 30.09.2019 with the rate 3200
• 2.  Concluded additional agreement from 01.12.2017 with higher rate - 3400, but its the same main agreement (date_end = 30.09.2019 and agreemetn  = 1)
• 3.  New additional agreement from 01.01.2018 , same rate - 3400 , same main agreement).
• ...
• 5. The mr_x canceled agreement 28.03.2019
• 6. The mrs_y conclude new agreement from 29.03.2019

Thanks for your attention!

[ziying35]

@edhans He made two posts on the same issue, and the link below contains the data text:

https://community.powerbi.com/t5/Power-Query/Start-Date-amp-End-Date-in-additional-agreements/m-p/12...