How to Filter similar Columns Based on Specific Conditions

[v-tangjie-msft]

Scenario:
In some cases, we may need to construct a table that contains only those entries that satisfy certain filtering criteria. The purpose of this article is to illustrate how to use the Dax or M function to achieve this function.

Sample Data:
This is a table of records of country regions showing the abbreviations and names of the various countries. For the sample data, we need to extract the value of the Value column for each ID when the value of the Name column is "N/A".

How:

Solution 1:
For a given table our first thought is to use the Dax expression, through the SWITCH() statement with the SUMMARIZE() function to create the table that can be created for the target results.

Table2 =

SUMMARIZE(

    ADDCOLUMNS(

        'Table',

        "Value", SWITCH(TRUE(),

            'Table'[Name1] = "N/A", 'Table'[Value1],

            'Table'[Name2] = "N/A", 'Table'[Value2],

            'Table'[Name3] = "N/A", 'Table'[Value3],

            'Table'[Name4] = "N/A", 'Table'[Value3]

        )

    ),

    'Table'[ID], [Value]

)

Solution 2:
For the above DAX expression, although its logic is clear and easy to understand, it inevitably involves the process of creating a new table. In small data volume projects, this is indeed a convenient and efficient method. However, when working with large datasets, using DAX to create a new table may place a considerable burden on computer memory, which in turn leads to problems of data duplication and redundancy. With this in mind, we turn our attention to M function, which excels in data cleaning and transformation and is a much better alternative.
What follows is an attempt to accomplish the above goal using native M Functions and custom functions.

let

    Source = …,

    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"ID"}, "Attribute", "Value"),

    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Unpivoted Other Columns", {{"Attribute", each Splitter.SplitTextByCharacterTransition((x)=> not List.Contains({x}, {"0".."9"}), {"0".."9"})(_){0} , type text}}),

    #"Grouped Rows" = Table.Group(#"Extracted Text Before Delimiter", {"ID"}, {{"Value", each _{List.PositionOf([Value], "N/A")+1}[Value] , type text}})

in

    #"Grouped Rows"

Next, I will analyze this code line by line:
1. #"Unpivoted Other Columns": This line of code unpivots all the columns in the "Source" table except "ID.

2. #"Extracted Text Before Delimiter": This line of code uses the Splitter.SplitTextByCharacterTransition function to split the text according to the character transition, to find the first character that is not a number, and then split the text, and then take the result of the split of the first element.
    (1) Table.TransformColumns: This function is used to convert columns in a table. It accepts two parameters: the table to be converted and a set of conversion instructions.
    (2) Splitter.SplitTextByCharacterTransition: This function is used to split text based on character conversion. It takes two arguments: a judgment function and a character set. The judgment function is used to determine when a character conversion occurs, and the character set specifies the characters that may cause the conversion.
    (3) (x)=> not List.Contains({x}, {"0".."9"}): This is an anonymous function that checks if the current character x is not in the numeric character set "0"..." 9". If so, it returns true, indicating that the text should be split here.
    (4) {"0".."9"})(_){0}: This is an indexed access to the result of the Splitter.SplitTextByCharacterTransition function. It selects the first element in the split result, the part of the text before the number, where the first {"0"..." 9"} defines which characters are considered to be numbers and is used to determine the trigger conditions for text splitting. For the second {"0"..." 9"} tells the function that the split operation should be performed when a character in the text changes from non-numeric to numeric.

3.#"Grouped Rows": This line of code groups the table according to the "ID" column and creates a new column "Value" for each grouping. The value of this new column is determined by finding the position of "N/A" in the "Value" column and taking the value of an element after that position.
The code _{List.PositionOf([Value], "N/A")+1}[Value] is an anonymous function applied to each group. It first uses the List.PositionOf function to find the position of the first occurrence of "N/A" in the Value column. Then, it adds 1 to this position to get the position of the first value after "N/A". Finally, it uses this position to select the corresponding value in the Value column.

Solution 3:
However, when we use M-functions, the native functions often don't meet our requirements, or we have to take a long detour to get to our destination. But we can further simplify this query by writing it using Lambda-style syntax.

For example, the following M function:

let

    Source =…,

    Transform = Table.FromRows(

       List.TransformMany(Table.ToRows(Source),

            each {List.PositionOf(_, "N/A")},

            (x,y)=> {x{0}} & List.Transform({y+1}, (a)=> x{a} ) ),

       type table[Article number=text, Value=text] )

in

    Transform

Next, I will analyze this custom function:

1.Table.ToRows(Source): This converts the Source table into a list of rows, where each element is a record containing data for each row in the table.

2.each {List.PositionOf(_, "N/A")}: This parameter is a function that is applied to each row returned by Table.ToRows(Source).The List.PositionOf function is used to find the position of the string "N/A" in the row. This position will be used as the basis for subsequent processing.

3. (x,y)=> {x{0}} & List.Transform({y+1}, (a)=> x{a} ) ): It defines how to generate new elements based on the position found by List.PositionOf. This function takes two arguments:X is the current row and Y is the position of "N/A" in the row.
    (1) {x{0}}: This extracts the first element from the current line X, i.e. "ID".
    (2) List.Transform({y+1}, (a)=> x{a} ): This part of the code extracts the "Value" based on the "N/A" position y. The {y+1} is because the "Value" column is after the " Name " column, so we need to add 1 to the position we find. List.transform function then extracts the element at this position from row X.
To summarize, the List.TransformMany function iterates through each row of the Source table, finds the position of "N/A", and then creates a new record for each row containing the "ID" and the corresponding "Value". Finally, the Table.FromRows function converts this new list into a table with two columns: "ID" and "Value".

In this way, you can see that you can achieve the result we want using only one step.

Summary:
While there are many ways to dynamically generate similar columns that match the filtered values, we should consider the simplicity, amount of data, and operational feasibility, and wisely use the appropriate methods based on the characteristics of the data.

Author: Albert H. 

Reviewer: Ula and Kerry 

How to Filter similar Columns Based on Specific Conditions.pbix