Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Date questions controle end date

Hi everyone, I have a problem. I have a table with an ID, Date en need to get a colum End. End must be the value of the next Date with the same ID. If there is no next the the end date must be the s...
Tips and Tricks
  • ronrsnfld's avatar
    ronrsnfld
    3 years ago

    In Power Query, try the following code

    •  Group by ID
    • Add a column to each subgroup consisting of the date column altered by
      • Removing the first entry
      • Duplicating the last entry
    let

//change next line to reflect actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table7"]}[Content],

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Date", type date}}),

//Group by ID
//Then shift the date column up one (delete first entry,
//  adding the "last" date to the bottom
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {
        {"End", each
            Table.FromColumns(
                Table.ToColumns(_) &
                {List.RemoveFirstN([Date],1) & {List.Last([Date])}},
                {"ID","Date","End"}),
                type table[ID=Int64.Type,Date=date, End=date]}
    }),
    #"Expanded End" = Table.ExpandTableColumn(#"Grouped Rows", "End", {"Date", "End"})
        
        
in
    #"Expanded End"

    Results from your Data above

     

     

Nathaniel_C's avatar
Nathaniel_C
Icon for Community Champion rankCommunity Champion
3 years ago

Hi Anonymous ,
If I understand you correctly, please try this

My original table is on the left, the solution is the table on the right

End = 

Var _id = MAX(NextDate[ID])
Var _date = MAX(NextDate[Date])

var _calc = CALCULATE(MIN(NextDate[Date]),FILTER(ALL(NextDate),NextDate[ID]=_id && NextDate[Date]>_date))

return if(ISBLANK(_calc),_date,_calc)

My table name is NextDate

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

 

ronrsnfld's avatar
ronrsnfld
Icon for Super User rankSuper User
3 years ago

In Power Query, try the following code

  •  Group by ID
  • Add a column to each subgroup consisting of the date column altered by
    • Removing the first entry
    • Duplicating the last entry
let

//change next line to reflect actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table7"]}[Content],

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Date", type date}}),

//Group by ID
//Then shift the date column up one (delete first entry,
//  adding the "last" date to the bottom
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {
        {"End", each
            Table.FromColumns(
                Table.ToColumns(_) &
                {List.RemoveFirstN([Date],1) & {List.Last([Date])}},
                {"ID","Date","End"}),
                type table[ID=Int64.Type,Date=date, End=date]}
    }),
    #"Expanded End" = Table.ExpandTableColumn(#"Grouped Rows", "End", {"Date", "End"})
        
        
in
    #"Expanded End"

Results from your Data above

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Thats that works, very nice and again thanks. Just one detailed question. I am not very familiar with Power Query and the Advanced editor. The dataset that I shared was an simplied version. What do I need to do if I want to add addtional columns that are in the base dataset (about 20 colums)?

     

    Hope you can awnser this as wel 😄 

    • ronrsnfld's avatar
      ronrsnfld
      Icon for Super User rankSuper User
      3 years ago

      You may need to specify all the columns in the functions where only a few are specified now. Your own code should have generated a #"Changed Type" step that labels them there. You will have to add more column definitions in the Table.FromColumns function. There are methods to automate that but it depends on your column types, or you can just add them manually if they will be a constant.