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]))