Forum Discussion

Lucian's avatar
Lucian
Responsive Resident
5 years ago
Solved

Power Query Select rows between two TEXT values

Hello, I have a problem selecting table rows that are between two text values, and I hope will find someone to point me to the right direction.   I have an table that has one of the columns "Accou...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Lucian 

     

    It is a better explanation now. The original list is your raw data, you need to filter based on your start and end. So your start and end always have the same format - same amount of dot? If yes, here is one way

    start and end is your input

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjU0MtQzMNQzNFWK1UHimqFyzVG5FqhcSzjXwBCJA5IzMkDlGiIpReKA5YyQuEZAEVQumqwxKtcElQv0TSwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Account No" = _t]),
    Input = [start="5121.01.19",end ="5121.02.03"],
    #"Filtered Rows" = Table.SelectRows(Source, each List.Contains({Number.From(Text.Remove(Input[start],"."))..Number.From(Text.Remove(Input[end],"."))},Number.From(Text.Remove([Account No],"."))) and  Text.Length(Text.Select([Account No],{"."}))=Text.Length(Text.Select(Input[start],{"."})) )
in
    #"Filtered Rows"

     

    if I change [start="51210119",end ="51210203"], then

    to make it easy to understand, I put the Input here in the query, you may have other ways to get the input - another query for example

     

     

     

wdx223_Daniel's avatar
wdx223_Daniel
Community Champion
5 years ago

Table.SelectRows(PreviousStepName,each let a=Text.Remove([Account No],".") in a>="51210119" and a<="51210203")

  • Lucian's avatar
    Lucian
    Responsive Resident
    5 years ago

    Hi wdx223_Daniel ,

     

    Thank you for your quick response, but as I have mentioned in my initial post I cannot use replace to eliminate the dots and treat all accounts as numeric values because there are similar accounts 5121.01.19 that is NOT the same as 51210119.

    Your formula will return these values:

    But for a selection like "5121.01.19..5121.02.03" the resulting rows 2 & 5 should not be considered "valid".

     

    Anonymous: Besides the above explanation, I could add that I would like the result respect the same "mask" "nnnn.nn.nn".

    If the account could be considered a three part number "nnnn(1).nn(2).nn(3)" , I would like to use "between" for each of the 3 sections of the account like this:

    between MIN(Part1) and MAX(Part1)

    and

    between MIN(Part2) and MAX(Part2)

    and

    between MIN(Part3) and MAX(Part3)

     

    Except that the account does not always have three parts - could have only 2 parts (5121.01) or even a single one (512101).

     

    Is this possible in this way?

     

    Kind Regards,

    Lucian

    • wdx223_Daniel's avatar
      wdx223_Daniel
      Community Champion
      5 years ago

      Table.SelectRows(PreviousStepName,each let fx=(txt1,txt2)=>List.Count(Text.Split(txt1,"."))=List.Count(Text.Split(txt2,".")) and Text.Remove(txt1,".")<=Text.Remove(txt2,".") in fx([Account No],"5121.01.19") and fx("5121.02.03",[Account No]))

      • Lucian's avatar
        Lucian
        Responsive Resident
        5 years ago

        Hi wdx223_Daniel ,

         

        Thank you again for your quick response.

        I have tried to reproduce your example, and just to be easy to follow I will put the complete queries.

         

        So, first the Accounts table, which I am sorry I didn't put it from the beginning:

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjU0MtQzMNQzNFWK1UHimqFyzVG5FqhcSzjXwBCJA5IzMkDlGiIpReKA5YyQuEZAEVQumqwxKtcElQv0TSwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Account No" = _t])
in
    Source

         

        Now the results with your code, after correcting a minor typo fx([Account No],"5121.02.03") instead of the "original" fx("5121.02.03", [Account No]) (which returned blanks):

         

        let
  Source = Accounts, 
  ResultV2 = Table.SelectRows(Source,each let fx=(txt1,txt2)=>List.Count(Text.Split(txt1,"."))=List.Count(Text.Split(txt2,".")) and Text.Remove(txt1,".")<=Text.Remove(txt2,".") in fx([Account No],"5121.01.19") and fx([Account No],"5121.02.03"))
in
  ResultV2

        Will return completely wrong results:

        Maybe the problem arise because of the "eding" of the accounts "01.19"<="02.03"?... or I didn't use your code correctly?

         

        Kind Regards,

        Lucian

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Lucian 

       

      Your sample is to get it, right? List.Contains to act like your between, but add a Count "." to filter out those invalid rows - it is only for your sample data, I don't get what you mean by it- you need to observe your data

      Except that the account does not always have three parts - could have only 2 parts (5121.01) or even a single one (512101).

       

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjU0MtQzMNQzNFWK1UHimqFyzVG5FqhcSzjXwBCJA5IzMkDlGiIpReKA5YyQuEZAEVQumqwxKtcElQv0TSwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Account No" = _t]),
    #"Filtered Rows" = Table.SelectRows(Source, each List.Contains({51210119..51210203},Number.From(Text.Remove([Account No],".")))and  List.Count(Text.Split([Account No],"."))>1 )
in
    #"Filtered Rows"

       

      • Lucian's avatar
        Lucian
        Responsive Resident
        5 years ago

        Hi Anonymous ,

         

        Thank you for your response.

        I am sorry that I did not explained better... because the ideea is not to "filter out" the records that contained ".".

         

        Case1: Sometimes I need to extract a list between: "5121.01.19..5121.02.03" and the results should be:

        5121.01.19

        5121.01.20

        5121.01.21

        5121.01.22

        5121.02.01

        5121.02.02

        5121.02.03

         

        Case 2: Sometimes I need to extract a list between "51210119..51210121" that should return

        51210119

        51210121

         

        With other words, in the accounts generated, sometimes there are "plain numbers", sometimes using a single dot and make them looks like a decimal number "5121.01", and sometimes will use multiple dots like "5121.01.12".

        So I need to select a "range of accounts" from an "start account" till an "end account" depending some report definition.

         

        Kind Regards,

        Lucian