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 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?
