Creating date column for new rows

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