Unfilter specific text which is only 1 word

[Anonymous]

Hi,

Any way I can unfilter the last value 'Column272'?

but not <> 'Column272' itselt since there will be more columns 273, 274 in the future,

so shouldn't be name specific values.

Does not contain 'Column' or '-' or

Begin with '20' also not working..

[Jai-Rathinavel]

Hi @Anonymous ,

I have tested with a sample data and applied basic text filter "does not contain", I am able filter out columns which are not starting with "Column" as value

Before applying text filter:

Text filter condition:

Table.SelectRows(#"Changed Type", each not Text.Contains([Value], "Column"))

After applying text filter:

I would suggest reopening the PBIX file and apply the above logic.

Thanks,

Jai 🙂

[Anonymous]

still not working...

[Jai-Rathinavel]

@Anonymous , Go to the next step Changed Type1 and check for "Column" value under week column. It shouldn't be there.

Thanks,

Jai

[Anonymous]

same, it's weird

[Jai-Rathinavel · Accepted solution]

@Anonymous , This might be a bug 

1. Try refreshing the data and loading it into the data model and check the values in frontend 

2. Duplicate the query and apply the same filter again. 

3. Try clearing the data cache located under the below setting

[Anonymous]

Thanks, i think it is a bug as it is not in frontend. 

[hnguy71]

Hi @Anonymous 

That's possible.

See my sample below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjdUitUBUkZgyjk/pzQ3z8jcWCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Week = _t]),
    
    // Step 1: Start with a sub-query to filter rows where Week starts with "Column"
    FilteredRows = Table.SelectRows(Source, each Text.StartsWith([Week], "Column")),
    
    // Step 2: Extract numerical part from "ColumnXXX"
    ExtractNumber = Table.AddColumn(FilteredRows, "NumericValue", each Number.FromText(Text.AfterDelimiter([Week], "Column")), type number),
    
    // Let's find  the max value
    FindMax = List.Max(Table.Column(ExtractNumber, "NumericValue")),

    // Step 4: Refer back to step before the sub-query and filter out the max value
    DynamicFilter = Table.SelectRows(Source, each [Week] <> "Column" & Text.From(FindMax) )
in
    DynamicFilter

Input:

Output (only filtering out max value for "column")

Here's an alternate solution that looks for a pattern:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtENNzBUitWBc4zAHOf8nNLcPCNzY6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Week = _t]),
    
    // Step 1: Let's check for a pattern
    AddPatternCheck = Table.AddColumn(Source, "IsMatch", each 
        
        // check for length of string
        Text.Length([Week]) = 8 and

        // is the first 4 a numeric value?
        Text.Select(Text.Start([Week], 4), {"0".."9"}) = Text.Start([Week], 4) and

        // how about the last 2?
        Text.Select(Text.End([Week], 2), {"0".."9"}) = Text.End([Week], 2),
        type logical
    ),

    // Step 2: Keep rows where pattern matches
    FilteredTable = Table.SelectRows(AddPatternCheck, each [IsMatch] = true),
    RemoveIsMatch = Table.RemoveColumns(FilteredTable,{"IsMatch"})
in
    RemoveIsMatch

Input:

Output:

[Anonymous]

sorry cannot understand first part of 'let' with random alphabets..