Forum Discussion
Power Query Select rows between two TEXT values
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
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]))
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 SourceNow 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 ResultV2Will 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
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