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 same as the date.

 

I struggel with this a lot, does someone have the awnser?

 

The picture below gives a simple overview. The yellow colum is what I am trying to make

 

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

     

     

6 Replies

  • Nathaniel_C's avatar
    Nathaniel_C
    Community 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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi Nathaniel_C thanks for the quick response. This is a calculated colum I can see based on the DAX writing style. I need this to be done within the query editor due to other calculations here that need this value. Do you have knowlages how to do this within the query editor?

      • Nathaniel_C's avatar
        Nathaniel_C
        Community Champion
        3 years ago

        Hi Anonymous ,
        This is actually a Dax measure, not a calculated column.  
        Sorry that I did not do it in Power Query, sometimes I forget which forum I am looking at.
        The people that are my go to people for Power Query include KenPuls and ImkeF . Perhaps one of them can answer this.

        Thank you,

         

        Nathaniel

    • ronrsnfld's avatar
      ronrsnfld
      Super 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 😄