Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Add end date to row

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?

 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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"

     

     

3 Replies

  • Mariusz's avatar
    Mariusz
    Community Champion
    6 years ago

    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.
    Mariusz Repczynski

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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"