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

     

     

     

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