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.
I would approach this by first stripping off the excess digits at the end using Split By > Non-Digit to Digit and then from there pad left for the digits and pad right for the letters for comparison.
Here's a full sample query you can paste into the Advanced Editor:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZPBbsIwDIZfJeppSCDFYeng6OS0w3ZFWsUhqlKKVNGp7XbmMbbX40kWh8LKcLRDo+r/Etux/xRFpjGbZ9qExbaNMGLXeTf4LtvOi2xlg7zCkeENU0EFliyD+sjGk0GVyCIKR9+LG8ra91HLaesTXxkVvLZpBpJlOsbUmi0c5JooJM5KICoVfwEZGylznupJT5huJXqyRLqK4pup4tCUTowHNkTBshTNpd/3cdFCki2ShNQFm0uGvikKGX8mMxYPz7tD23kx1F58uuYjSK4Kx8Tp+IWn4/fsahrzb2SjgB+LBPubPf8b56aGsnadK4N6qYPEat/1g+j9INpKYPNeO2Gv+2YTw/H5I1sljIppo45mi+v0TURHGFJf24M/b1VnX/JveKRgJO+TWAO+be7p9gc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Col A" = _t, #"Col B" = _t, #"Manual Output (Greater Column)" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Col A", type text}, {"Col B", type text}, {"Manual Output (Greater Column)", type text}}),
#"Split Column by Character Transition" = Table.SplitColumn(#"Changed Type", "Col A", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Col A", "DeleteA"}),
#"Split Column by Character Transition1" = Table.SplitColumn(#"Split Column by Character Transition", "Col B", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Col B", "DeleteB"}),
#"Removed Columns" = Table.RemoveColumns(#"Split Column by Character Transition1",{"DeleteA", "DeleteB"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each
let
PadLength = 6,
DigitsA = Text.PadStart(Text.Select([Col A], {"0".."9"}), PadLength, "0"),
DigitsB = Text.PadStart(Text.Select([Col B], {"0".."9"}), PadLength, "0"),
LettersA = Text.PadEnd(Text.Remove([Col A], {"0".."9"}), PadLength, "0"),
LettersB = Text.PadEnd(Text.Remove([Col B], {"0".."9"}), PadLength, "0"),
Result = if [Col A] = [Col B] then "Matches"
else if DigitsA & LettersA > DigitsB & LettersB then "Col A greater"
else "Col B greater"
in
Result,
//DigitsA & LettersA & " ? " & DigitsB & LettersB,
type text
)
in
#"Added Custom"
With PadLength = 3, the comparison looks like this (using the commented-out line instead of Result in the above):
Works well and looks simple. Understood the use the of the pad length.
However it fails where if either of the Col A or B is just simply '0' and comparing this zero with a alpha (or alphanumeric value), it results in the alpha/alphanumeric value being greater than '0'.
i.e. (row 24)
Col A = 0
Col B = B
Output (to the naked eye) should be Col A is greater, however its outputting Col B is greater.
Would adding additional else if in the 'Result' be a solution? If so, what condition can be added?
Thanks
Try using "z" instead of "0" to right pad the LettersA and LettersB variables.
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?