Forum Discussion
Help with Query (Greater than)
NewStep= let fx=(t)=>let a=Text.From(t),b=List.PositionOf(Text.ToList(a&"a"),"",0,each List.Contains({"0".."9"},_)),c=Splitter.SplitTextByPositions({0,b})(a) in {Number.From(c{0}),Text.Start(c{1},1)} in Table.AddColumn(PreviousStepName,"Check",each let a=fx([Col A]),b=fx([Col B]),c=if a{0}<>b{0} then Value.Compare(a{0},b{0}) else Value.Compare(a{1},b{1}) in if a{0}=null and b{0}=null then "None" else if c=-1 then "Col B greater" else if c=0 then "Matches" else "Col A greater")
OK. It looks like my initial suggestion of padding with "0" was closer to correct. The difficulty is that the padding expands "A" and "0A" to be the same thing but you want these treated differently when compared to "0". I.e., "0" > "A" but "0" < "0A".
Try this with the digits and the letters treated separately:
let DigitsA = Number.FromText(Text.Select([Col A], {"0".."9"})) ?? -1, DigitsB = Number.FromText(Text.Select([Col B], {"0".."9"})) ?? -1, LettersA = Text.Remove([Col A], {"0".."9"}), LettersB = Text.Remove([Col B], {"0".."9"}), Result = if [Col A] = [Col B] then "Matches" else if DigitsA > DigitsB then "Col A greater" else if DigitsA = DigitsB and LettersA > LettersB then "Col A greater" else "Col B greater" in ResultNote: The ?? -1 syntax replaces nulls with -1.
Thanks AlexisOlson. Replacing the "0" with "z" fixed the Row 24 record, but then it gives error for Row 5 (where Col A is "0" and Col B is "0A").
I would certainly expect "0" > "0A", just like "A" > "AA". Can you explain why you disagree?
That is the logic I have in use for my scenario. AlphaNumerical is always greater than Numerical (provided the Numerical value from start/left_most are same).
i.e.
- If both values (Col A and Col B) are only Numerical, then thats pretty simple - standard numerical comparison
- if one of the values is AlphaNumerical and the other is Numerical, then comparison is done on the starting/left_most numerical values (prior to alpha). If the numerical values are same, then it looks into the alpha values.
so here if the values are "0" and "0A", the numerical value is same, but alpha A makes the "0A" greater.
- if one value is only alpha and other is numerical, then numerical is greater (i.e. 0>A)
- if both values are only alpha, then B>A , C>A, AA > A, AA >B (i.e. sequence would be A-Z, then AA-AZ, BA-BZ, CA-CZ....AAA-AAZ, BAA-BAZ and so on.
It's just the logic I'm having to use for my scenario.
OK. It looks like my initial suggestion of padding with "0" was closer to correct. The difficulty is that the padding expands "A" and "0A" to be the same thing but you want these treated differently when compared to "0". I.e., "0" > "A" but "0" < "0A".
Try this with the digits and the letters treated separately:
let DigitsA = Number.FromText(Text.Select([Col A], {"0".."9"})) ?? -1, DigitsB = Number.FromText(Text.Select([Col B], {"0".."9"})) ?? -1, LettersA = Text.Remove([Col A], {"0".."9"}), LettersB = Text.Remove([Col B], {"0".."9"}), Result = if [Col A] = [Col B] then "Matches" else if DigitsA > DigitsB then "Col A greater" else if DigitsA = DigitsB and LettersA > LettersB then "Col A greater" else "Col B greater" in ResultNote: The ?? -1 syntax replaces nulls with -1.
Works perfectly 🙂 Thanks a lot. This saves me an extra set of coding!
Much appreciated 😀