Creating date column for new rows

[analyst85]

Hi

I have a table that has a list of categories alongwith account IDs, each account ID will have a varying number of categories, and we have a date column for an account active date.

Unfortunately there is no timestamp as to when a new category appears in the table, is there a way in Power Query that I can create a column that will auto assign a date when a new category row is added (ideally the date when it first appeared such as a refresh), and where there is no date for pre existing categories, it will default to the account active date?

Any help would be greatly appreciated.

[johnbasha33 · Accepted solution]

@analyst85 

To achieve this in Power Query, you can follow these steps:

1. Load your table into Power Query.
2. Sort the table by the "Account ID" and "Date" columns.
3. Add an index column to track the order of rows within each account group.
4. Add a custom column to check if the current row's category is different from the previous row's category for the same account.
5. Use List.Accumulate to keep track of the date when a new category appears for each account.
6. Fill down the newly created column to assign the date to rows with the same category as the first appearance.
7. Replace null values in the new column with the "Account Active Date" column.

Here's the step-by-step implementation in Power Query:

let
// Load your table
Source = ...,

// Sort by Account ID and Date
SortedTable = Table.Sort(Source,{{"Account ID", Order.Ascending},{"Date", Order.Ascending}}),

// Add an index column
AddIndex = Table.AddIndexColumn(SortedTable, "Index", 0, 1, Int64.Type),

// Add custom column to check for new categories
AddNewCategoryFlag = Table.AddColumn(AddIndex, "NewCategoryFlag", each if [Account ID] = AddIndex{[Index]-1}[Account ID] and [Category] <> AddIndex{[Index]-1}[Category] then 1 else 0),

// Use List.Accumulate to keep track of new category dates
AddNewCategoryDate = Table.AddColumn(AddNewCategoryFlag, "NewCategoryDate", each
let
CategoryDates = List.Accumulate(
{0..[Index]},
{},
(state, current) =>
let
Date = if AddNewCategoryFlag{current}[NewCategoryFlag] = 1 then AddNewCategoryFlag{current}[Date] else null
in
state & {Date}
)
in
List.Last(CategoryDates)),

// Fill down the new category date column
FillDownNewCategoryDate = Table.FillDown(AddNewCategoryDate,{"NewCategoryDate"}),

// Replace null values with Account Active Date
ReplaceNullWithActiveDate = Table.ReplaceValue(FillDownNewCategoryDate,null, each [Account Active Date], Replacer.ReplaceValue,{"NewCategoryDate"})
in
ReplaceNullWithActiveDate

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

[analyst85]

Thanks this is perfect and a great solution