Forum Discussion

almustafa's avatar
almustafa
Regular Visitor
2 years ago

Data Cleaning Condations for More than 160 Columns and 1000 Rows

Hi, 

I hope you are doing well, 

 

I am looking for a solution to apply more than 170 if conditions on 160 columns and more than 1000 rows. Where I have an assessment, and I would to apply logical conditions between two or more columns and add it in a column and give an error message if the values are not matching with the logic I put.

for example:
If(AND(x column value = "Yes", m column value = "No"), "Issue in the values between x and y", "Good").

Later create a table with same columns title of Source file or with a new name showing the issue. And add to each row of new table the index of the row in the organial data source to correct it or add a link to that row.

Is it possible to read a formula from a table and apply it to each row as on solution?

 

Later I would to remove from the result table all columns with no issue and remove all rows with no issue too. 

 

Note: I am not looking for a solution to create a column for each condition 🙂

Thanks for your support in advance!

Best Regards

 

 

Need Help
Tips and Tricks

5 Replies

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Icon for Super User rankSuper User
    2 years ago

    Hello almustafa,

     

    Can you please try the following:

     

    1. Define Conditions in a Table and load both your source data and the table into Power Query.

    ConditionID Column1 Column2 Condition ErrorMessage
    1 x m x = "Yes" AND m = "No" Issue in the values between x and m
    2 a b a > b a is greater than b

     

    2. Apply Conditions Dynamically

    (row as record, conditions as table) as record =>
let
    ErrorMessages = List.Generate(
        () => [i = 0, ErrorList = {}],
        each [i] < Table.RowCount(conditions),
        each [
            i = [i] + 1,
            ErrorList = if try Expression.Evaluate(Record.Field(row, conditions[Column1]{[i]}) & " " & conditions[Condition]{[i]} & " " & Record.Field(row, conditions[Column2]{[i]})) then [ErrorList] else List.InsertRange([ErrorList], List.Count([ErrorList]), {conditions[ErrorMessage]{[i]}})
        ],
        each [ErrorList]
    )
in
    if List.Count(ErrorMessages{0}[ErrorList]) = 0 then null else Record.AddField(row, "Errors", Text.Combine(ErrorMessages{0}[ErrorList], "; "))

    You can invoke this function on your data table

    let
    Source = YourDataTable,
    ConditionsTable = YourConditionsTable,
    ApplyConditions = Table.AddColumn(Source, "Errors", each EvaluateConditions(_, ConditionsTable)),
    FilteredRows = Table.SelectRows(ApplyConditions, each [Errors] <> null),
    RemoveEmptyColumns = Table.RemoveColumns(FilteredRows, List.Select(Table.ColumnNames(FilteredRows), each List.NonNullCount(Table.Column(FilteredRows, _)) = 0))
in
    RemoveEmptyColumns

    Hope this helps!

    • almustafa's avatar
      almustafa
      Regular Visitor
      2 years ago

      Dear Sahir_Maharaj 

      Thanks for your reply and support!

      This is great, it is almost the solution I am looking for. I got this error after I applied the code.

       

      An error occurred in the ‘’ query. Expression.Error: We cannot apply field access to the type List.
      Details:
      Value=[List]
      Key=ErrorList

       

       

      Attached the Excel file as an example and the .pbix file in this link (please feel free to upload to same folder). 

      Advance

       

      Thanks for your support!

      Best Regards 

      Mahmoud

      • almustafa's avatar
        almustafa
        Regular Visitor
        2 years ago

        Hi dear Sahir_Maharaj 

        Thanks for your support! 

        I am still getting same error, the files (BI and Excel) uploaded to the folder. These are the errors message images.

         

        Query Editor

         

         

        Close and Apply Error image:

         

        Thanks for your follow up!

        Best Regards 

        Mahmoud

         

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Icon for Super User rankSuper User
    2 years ago

    Hello almustafa,

     

    Can you please try the following:

     

    1. Define Conditions in a Table and load both your source data and the table into Power Query.

    ConditionID Column1 Column2 Condition ErrorMessage
    1 x m x = "Yes" AND m = "No" Issue in the values between x and m
    2 a b a > b a is greater than b

     

    2. Apply Conditions Dynamically

    (row as record, conditions as table) as record =>
let
    ErrorMessages = List.Generate(
        () => [i = 0, ErrorList = {}],
        each [i] < Table.RowCount(conditions),
        each [
            i = [i] + 1,
            ErrorList = if try Expression.Evaluate(Record.Field(row, conditions[Column1]{[i]}) & " " & conditions[Condition]{[i]} & " " & Record.Field(row, conditions[Column2]{[i]})) then [ErrorList] else List.InsertRange([ErrorList], List.Count([ErrorList]), {conditions[ErrorMessage]{[i]}})
        ],
        each [ErrorList]
    )
in
    if List.Count(ErrorMessages{0}[ErrorList]) = 0 then null else Record.AddField(row, "Errors", Text.Combine(ErrorMessages{0}[ErrorList], "; "))

    You can invoke this function on your data table

    let
    Source = YourDataTable,
    ConditionsTable = YourConditionsTable,
    ApplyConditions = Table.AddColumn(Source, "Errors", each EvaluateConditions(_, ConditionsTable)),
    FilteredRows = Table.SelectRows(ApplyConditions, each [Errors] <> null),
    RemoveEmptyColumns = Table.RemoveColumns(FilteredRows, List.Select(Table.ColumnNames(FilteredRows), each List.NonNullCount(Table.Column(FilteredRows, _)) = 0))
in
    RemoveEmptyColumns

    Hope this helps!