Forum Discussion
Creating a column retrieving the closest previous date with conditions (Example shown)
Hi CaroReglisse
If you are happy with a DAX method, you can create a new column with the following DAX formula:
Column = IF('Table'[Type]="B", VAR curDate = 'Table'[Date] VAR preDate = CALCULATE(MAX('Table'[Date]), 'Table'[Date]<curDate, ALL('Table')) VAR preDateRow = CALCULATETABLE('Table','Table'[Date]=preDate, ALL('Table')) VAR preType = MAXX(preDateRow,'Table'[Type]) VAR preRowNumber = MAXX(preDateRow,'Table'[Run number]) RETURN IF(preType<>"B", preRowNumber, 'Table'[Run number]), 'Table'[Run number])Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!
Yes, you can achieve this in Power Query by creating a custom column that references the closest previous date's run number, taking into account whether the closest previous date belongs to a row with Type = "B". Here's how you can do it step-by-step:
Steps to Create the New Run Number Column in Power Query
Load Your Data into Power Query:
- Open Power BI Desktop.
- Go to Home > Transform Data to open Power Query Editor.
- Load your dataset into Power Query.
Add a Custom Column:
- In Power Query Editor, go to the Add Column tab and select Custom Column.
Define the Custom Column Formula:
- You will create a formula that checks the conditions and assigns the appropriate Run Number.
Here's a detailed explanation of the steps to create the custom column:
Formula Breakdown
Find Closest Previous Date:
- For each row where Type is "B", you need to find the closest previous date of Type = "A" and retrieve its Run Number.
Check if Closest Previous Date Belongs to Type B:
- If the closest previous date itself is of Type = "B", you retain the current Run Number.
Power Query M Code
You can use the following Power Query M code to achieve this:
// Load your data
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
// Convert Date column to datetime type
ChangedType = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"Closest previous date", type datetime}}),
// Add a column to check if Type is "B"
AddIsTypeB = Table.AddColumn(ChangedType, "IsTypeB", each [Type] = "B"),
// Add a column to get the run number of the closest previous date
AddPreviousRunNumber = Table.AddColumn(AddIsTypeB, "New Run Number", each
let
currentRowDate = [Date],
currentRowType = [Type],
previousRows = Table.SelectRows(AddIsTypeB, each [Date] < currentRowDate and [Type] = "A"),
closestPreviousRow = Table.Sort(previousRows,{{"Date", Order.Descending}}),
closestPreviousRunNumber = if Table.RowCount(closestPreviousRow) > 0 then Record.Field(closestPreviousRow{0}, "Run number") else null,
closestPreviousType = if closestPreviousRunNumber <> null then Record.Field(Table.SelectRows(AddIsTypeB, each [Run number] = closestPreviousRunNumber){0}, "Type") else null
in
if currentRowType = "B" and closestPreviousType = "B" then [Run number] else closestPreviousRunNumber
),
// Remove helper columns if needed
RemoveColumns = Table.RemoveColumns(AddPreviousRunNumber,{"IsTypeB"})
in
RemoveColumns
Explanation of the M Code
- Source: Load your dataset.
- ChangedType: Ensure that the date columns are of datetime type.
- AddIsTypeB: Adds a column to check if the Type is "B".
- AddPreviousRunNumber: Adds a column to calculate the New Run Number based on the closest previous date. This includes:
- Finding previous rows with Type = "A".
- Sorting by date to get the closest previous date.
- Checking the type of the closest previous row.
- Assigning the run number based on whether the closest previous row is of Type = "B".
- RemoveColumns: Optionally removes the helper columns.
This approach ensures that each row with Type = "B" retrieves the appropriate Run Number based on the conditions specified.
If you encounter any issues or have more questions, feel free to ask!