Power Query Select rows between two TEXT values

[Lucian]

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 "Account No" of type text.

Account No
5121.01.15
5121.01.16
5121.01.17
5121.01.18
5121.01.19
51210119
5121.01.20
5121.01.21
51210121
5121.01.22
5121.02.01
5121.02.02
5121.02.03
5121.02.04
5121.02.05

Microsoft Dynamics NAV have a special notation for "between" operator like this: ".." (dot)(dot)

So using an expression like "5..9" means all the values between 5 and 9: 5,6,7,8,9

The same "between" operator for "Account No" colum like this: 5121.01.19..5121.02.03 would return the following list of accounts:

5121.01.19

5121.01.20

5121.01.21

5121.01.22

5121.02.01

5121.02.02

5121.02.03

It seems that NAV is sorting the column using text and get the values kind of between MIN(Text) and MAX(Text).

The main problem I have, because of the similarities between some accounts like 5121.01.19 and 51210119 I cannot just remove dots and treat them all as numbers.

So, is it possible in Power Query to get the values similar to the ".." (between) operator in NAV?

Kind Regards,

Lucian

[Vera_33 · Accepted solution]

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]

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

[Lucian]

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".

@Vera_33: 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

[Vera_33]

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]

Hi @Vera_33 ,

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

[Vera_33 · Accepted solution]

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

[Lucian]

Hi @Vera_33 ,

Yeeeeeei! 🎉

This is the desired result, and you get it right - the "start" and "end" will always have the same format.

I have test it even with another account formats like "5121.02..5121.04" and will return expected results. 👍

Now I think I could manage to tranform it into an function to suit my needs.

Thank you very very much for your time!

Kinr Regards,

Lucian

[wdx223_Daniel]

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]

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

[wdx223_Daniel · Accepted solution]

[Lucian]

Hi @wdx223_Daniel ,

Indeed, your solution work also, my mistake was "correcting" the wrong part of the formula.

Now, with your help I have more options. 🙂

Thank you for your time invested in helping me. 

Kind Regards,

Lucian

[Vera_33]

Hi @Lucian 

Power Query does have this, you can do {"a".."z"}, {5..9} - your sample. BUT, I don't get what you want. Can you explain what you want to achieve?