Forum Discussion
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...
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
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
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?