Forum Discussion
Creating a column retrieving the closest previous date with conditions (Example shown)
Hi all,
I am looking to create a new column in PowerBI/PowerQuery whose value will depend on 3 different columns.
This is the example dataset I have :
| New columns I need | |||||
| Run number | Step | Type | Date | Closest previous date | New Run Number |
| 1 | Step 1 | A | 26/07/2024 18:01:45 | 1 | |
| 1 | Step 2 | A | 26/07/2024 18:59:33 | 1 | |
| 2 | Step 3 | B | 27/07/2024 03:11:12 | 26/07/2024 18:59:33 | 1 |
| 3 | Step 1 | A | 27/07/2024 18:00:10 | 3 | |
| 3 | Step 2 | A | 27/07/2024 19:12:54 | 3 | |
| 4 | Step 3 | B | 28/07/2024 02:11:45 | 27/07/2024 19:12:54 | 3 |
| 5 | Step 1 | A | 28/07/2024 17:59:01 | 5 | |
| 5 | Step 2 | A | 28/07/2024 18:30:32 | 5 | |
| 6 | Step 3 | B | 29/07/2024 02:25:14 | 28/07/2024 18:30:32 | 5 |
| 7 | Step 3 | B | 29/07/2024 14:52:17 | 7 |
For each Type = "B" row, want to retrieve the run number associated with the closest previous date.
However, if the closest previous date belongs to a type = "B" row, then, no need to find the new associated date and we keep the same Run number (as it is the case for the last row).
I do not necessarily need two new columns, it is just to better explain what I mean.
I just care to retrieve the New Run Number column.
Is there a way to do this in PowerQuery ?
Thank you in advance
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!
2 Replies
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:
let
// 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
RemoveColumnsExplanation 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!
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!
