cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mvanwill Frequent Visitor
Frequent Visitor

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?

 

Sample.jpg

1 ACCEPTED SOLUTION

Accepted Solutions
mcybulski Member
Member

Re: Add end date to row

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"

 

 

View solution in original post

3 REPLIES 3
Super User
Super User

Re: Add end date to row

Hi @mvanwill 

 

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

 

mvanwill Frequent Visitor
Frequent Visitor

Re: Add end date to row

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

mcybulski Member
Member

Re: Add end date to row

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"

 

 

View solution in original post

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)