Hi Art666,
You are correct, and I apologize for the oversight. The two solutions I provided only compare the current row with the immediate previous row.
And yes you are right…unfortunately, this can become computationally expensive and slow down the query, especially with large datasets.
Here are a some alternate approaches
Approach 1: Iterative Row Comparison Using Custom Function
Description:
This approach involves creating a custom function to iterate through the list of values, comparing each value with all previous values in the list. If the current value is greater than any of the previous values, a "Yes" is returned; otherwise, "No" is returned.
Pros:
Straightforward logic and easy to understand.
Provides a clear comparison of each value with all previous values.
Cons:
Computationally expensive, especially for large datasets, as it involves nested iterations.
Complexity of f(n^2), where n is the number of rows, can lead to slow performance.
---
Create a Custom Function:
You'll want to create a custom function that iterates over all previous rows and checks if there's a smaller number in the specific column.
let
findSmaller = (currentRowIndex, columnName) =>
let
// Access all previous rows
previousRows = Table.FirstN(#"Your Previous Step Name", currentRowIndex),
// Get the current value
currentValue = #"Your Previous Step Name"{currentRowIndex}[columnName],
// Compare with all previous values
comparisonResults = List.Transform(Table.Column(previousRows, columnName), each currentValue > _)
in
// Check if any previous value is smaller
if List.Contains(comparisonResults, true) then "Yes" else "No"
in
findSmaller
Make sure to replace "Your Previous Step Name" with the actual name of the previous step in your query, and save the function with a name like findSmaller.
Apply the Custom Function:
You'll want to add an Index Column to your table to provide the current row index and then apply the custom function to each row.
let
// Add an Index Column
Source = Table.AddIndexColumn(#"Your Previous Step Name", "Index", 0, 1),
// Apply the custom function
Result = Table.AddColumn(Source, "IsSmallerThanPrevious", each findSmaller([Index], "ColumnName"))
in
Result
Replace "ColumnName" with the actual name of the column you want to check.
---
You'll need to ensure that the custom function and the main query are properly connected and that all the column and step names are correctly referenced. Make sure to test this on a smaller subset of your data first to ensure it's working as expected.
Approach 2: Minimized Comparison Using Accumulation Function
Description:
This approach uses the List.Accumulate function to iterate through the list of values, keeping track of the minimum value encountered so far. Each value is compared only with the minimum value, significantly reducing the number of comparisons.
Pros:
More efficient and faster, especially for large datasets.
Complexity of f(n), where n is the number of rows, provides a significant performance boost.
Avoids unnecessary comparisons by keeping track of the minimum value.
Cons:
Slightly more complex logic due to the use of the accumulation function.
Specific to the use case where you want to check if the current value is greater than any previous value.
---
Create a Custom Function:
let
findSmaller = (listOfValues) =>
let
// Initialize the minimum value variable
minValue = List.First(listOfValues),
// Iterate through the list and compare with the minimum value
result = List.Accumulate(listOfValues, {minValue, {}}, (state, current) =>
let
newMinValue = if current < state{0} then current else state{0},
comparisonResult = if current > state{0} then "Yes" else "No"
in
{newMinValue, state{1} & {comparisonResult}})
in
result{1}
in
findSmaller
Apply the Custom Function:
let
// Get the column of values you want to compare
columnOfValues = Table.Column(#"Your Previous Step Name", "ColumnName"),
// Apply the custom function to the entire column
comparisonResults = findSmaller(columnOfValues),
// Add the comparison results as a new column
Result = Table.AddIndexColumn(#"Your Previous Step Name", "Index", 0, 1),
ResultWithComparison = Table.FromColumns(Table.ToColumns(Result) & {comparisonResults}, Table.ColumnNames(Result) & {"IsSmallerThanPrevious"})
in
ResultWithComparison
Replace "ColumnName" with the actual name of the column you want to check, and "Your Previous Step Name" with the actual name of the previous step in your query.
Make sure to test this on a smaller subset of your data first to ensure it's working as expected.
---
Summary:
While Approach 1 provides a clear and direct way to compare each value with all previous values, it may not be suitable for large datasets due to its computational complexity. Approach 2, on the other hand, offers an optimized way to achieve the same result by minimizing the number of comparisons. It is more suitable for large datasets and provides a more efficient solution to the problem.
Additionally, if the comparison operation is complex and involves checking each value against all previous values, it may be more efficient to handle this task outside of Power Query. Here are a few alternatives that you might consider:
Using Excel Formulas (like you mentioned):
If you're working with Excel, you can use built-in Excel formulas to perform the comparison. For example, you could use an array formula with a combination of IF, MAX, ROW, and other functions to compare each value with all previous ones.
Pros:
Utilizes Excel's native calculation engine, which might be faster for this specific task.
Offers flexibility in designing the comparison logic.
Cons:
May become complex if the logic is intricate.
Not suitable if the data is not in Excel.
You can use an array formula to achieve this. Assuming the values are in column A, starting from A2, you can use the following formula in B2 and drag it down:
=IF(MAX($A$1:A1) < A2, "Yes", "No")
This formula compares the current value with the maximum of all previous values and returns "Yes" if it's greater, "No" otherwise.
Python (using Pandas):
You can use the Pandas library to efficiently compare each value with all previous ones.
import pandas as pd
# Sample data
data = {'Column1': [10, 20, 15, 25, 18]}
df = pd.DataFrame(data)
# Function to check if current value is greater than all previous values
def check_greater(row, df):
previous_values = df.loc[:row.name-1, 'Column1']
return 'Yes' if row['Column1'] > previous_values.max() else 'No'
# Apply the function to each row
df['Result'] = df.apply(lambda row: check_greater(row, df), axis=1)
print(df)
SQL:
In SQL, you can use a window function to achieve this comparison.
SELECT
Column1,
CASE
WHEN Column1 > MAX(Column1) OVER (ORDER BY ID ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
THEN 'Yes'
ELSE 'No'
END AS Result
FROM YourTable
ORDER BY ID;
This SQL code assumes that there's an ID column that defines the order of the rows. The MAX window function with ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING looks at all previous rows and compares the current value with the maximum of those.
