Add end date to row

[Anonymous]

Hello,

I hope you can help me with the following question:

I have a table who has only a start date. The end date of the production is the next start date of the production of the group customer/contract. Can you tell me what code I need to add to Power Query to add a column to have an extra column with the end date?

[Anonymous · Accepted solution]

The attached code is an alternate method that presumes you are using Excel Power Query. You can change the source to adapt to your data. The function OffsetCol is an inline function that efficiently gets the next row. A custom column is then used to detect if the Customer and Contract changes.

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    //(Source as table, ColName as text, optional offset as  number) =>
OffsetCol = (   Source as table,  RecordColumnName as text, optional Columns as list, optional  offset as number   ) =>
let
     offset = if offset = null then -1 else offset,
    // Prefix = if prefix = null then "Prior" else prefix,
   Columns = List.Buffer(if Columns = null then Table.ColumnNames(Source) else Columns), 

     SourceSelect = if Columns = null then Source else Table.SelectColumns(Source,Columns),

     ShiftedList = if offset < 0 then List.Repeat({null}, -offset)  &  List.RemoveLastN(Table.ToRecords(SourceSelect ),-offset)
                else List.RemoveFirstN(Table.ToRecords(SourceSelect ),offset) &  List.Repeat({null}, offset),
    
    Combine1 = Table.ToColumns(Source) & {ShiftedList},
    Combine2     = Table.FromColumns(Combine1, Table.ColumnNames(Source) & {RecordColumnName} )
 in
    Combine2,
    Result = OffsetCol(Source, "NextRecord", {"Start Date", "Customer","Contract"},1),
    #"Added Custom" = Table.AddColumn(Result, "EndDate", each if [NextRecord]=  null or [NextRecord][Contract] <> [Contract] or [NextRecord][Customer] <> [Customer] then  null else [NextRecord][Start Date]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"NextRecord"})
in
    #"Removed Columns"

[Mariusz]

Hi @Anonymous 

Try this 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WqlTSUaoAYiNDfQNDfSMDQ0ulWB0kYQuswgYm+gZGmMKGhtiFLVCEq4BC5SBhc+zCpvoGxliELRDCsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Customer = _t, Contract = _t, #"Start Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"Contract", type text}, {"Start Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "End Date", each let 
            sDate = Number.From( [Start Date] ),
            selectRows = Table.SelectRows( #"Changed Type", (r) => 
                Number.From( r[Start Date] ) > sDate 
                and r[Customer] = [Customer] 
                and r[Contract] = [Contract]
            )
        in 
            List.Min( selectRows[Start Date] ))
in 
    #"Added Custom"

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[Anonymous]

Hi,

After changing the formulatu I still get an error.

I fixed that I can group the records and put an index column in the table for each contract.  In  dax I can make a column where I can take the begin date as an end date but I would like to have it in Power Query.

Do you have a solution after I add the index?

Thanks,

Mario