Forum Discussion
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 "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
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
11 Replies
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?
Table.SelectRows(PreviousStepName,each let a=Text.Remove([Account No],".") in a>="51210119" and a<="51210203")
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
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]))
