Help with Query (Greater than)

[AliRade]

Hi All,

I have two columns with alphanumeric data type. I want to compare which column has the greater value. Sounds simple to the naked eye, but in Power BI query, I’m not getting the output I want.

I’ve added a very basic conditional column:

#"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Check_BI_1", each if [Col A] = [Col B] then "Matches" else if [Col A] > [Col B] then "Col A Greater" else "Col B Greater")

However I’m not getting the right output in certain cases, for example when Col A has value ‘8B’ and Col B has value ‘10’, the PBI_Query output is “Col A Greater” but Col B is actually greater to naked eye (due to numerical 10>8). Refer pbix file 

https://www.dropbox.com/s/d6acziw2a6ba9k0/Measure_Table_Filter.pbix?dl=0 

I’m THINKING one approach is that each of the values in Col A and Col B need to be split by delimiter such that the first few numerical characters is taken up until an alpha character and compare the numerical values. So Power BI Query will simply compare the ‘numerical’ values of Col A and Col B. If the numerical values are same, Then the query will look into comparing the Alpha characters.

So For example, if Col A = 58A and Col B = 58C:

1 – Split column by Delimiter which would result in Col A (numerical) = 58, Col A (Alpha) = A, Col B (Numerical) = 58, Col B (Alpa) = C

2 – Compare Col A (Numerical) with Col B (Numerical) and output

3 – IF the result from step 2 is ‘same’, then compare Col A (Alpha) with Col B (Alpha) and output. This should result in “Col B greater” and that’s the end of it.

4 – If the result from step 3 is again ‘same’ (i.e. if both Col A and Col B had values 58C), then output should simply be Same and that would be the end of it.

Difficulties:

• Trying to split column data by Delimiter; I want to only take the left most numerical characters up until the first Alpha characters.

Example, a Value 5121A will be 5121, a Value of 21A42 will be 21, a Value of A41 will be null (as no numerical character at left prior to alpha character)

This splitting of column data by delimiter defined is only because of the Logic Steps I have set out (Steps 1-4 above) to get the right output. Is there a better/simpler approach to this instead of the above steps?

With just the basic Conditional Column applied 9, I get the below “Check_BI_1” outputs (note the RED are incorrect output)

Col A	Col B	Manual Output (Greater Column)	Check_BI_1
5A	5B	Col B greater	Col B Greater
8C	8A	Col A greater	Col A Greater
2	1	Col A greater	Col A Greater
3	4	Col B greater	Col B Greater
0	0A	Col B greater	Col B Greater
2	2	Matches	Matches
6A	7	Col B greater	Col B Greater
8B	9C	Col B greater	Col B Greater
8B	10	Col B greater	Col A Greater
56A	55	Col A greater	Col A Greater
109A	110	Col B greater	Col B Greater
01A	02A	Col B greater	Col B Greater
05A	06A	Col B greater	Col B Greater
5	4	Col A greater	Col A Greater
3	4A	Col B greater	Col B Greater
23AB	24	Col B greater	Col B Greater
25B	25A	Col A greater	Col A Greater
21W	21C	Col A greater	Col A Greater
AB2	2	Col B greater	Col A Greater
AC1	2	Col B greater	Col A Greater
-	2	Col B greater	Col B Greater
2	-	Col A greater	Col A Greater
001A21	001A22	Matches (Ignore the values after ‘A’)	Col B Greater
0	B	Col A greater	Col B Greater
001A21	001B21	Col B greater	Col B Greater
001C21	001A26	Col A greater (Ignore the characters after the first set of Alpha Characters)	Col A Greater
8B	11	Col B greater	Col A Greater
8B	8C	Col B greater	Col B Greater
8A	10	Col B greater	Col A Greater
01A	01A	Matches	Matches
AB	BA	None	Col B Greater
02A	02B	Col B greater	Col B Greater
02A	01B0	Col A greater	Col A Greater
2A	1AZW	Col A greater	Col A Greater

[wdx223_Daniel · Accepted solution]

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

[wdx223_Daniel · Accepted solution]

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

[AliRade]

Thanks Daniel, this gives the desired output. Now going to spend a bit of time to comprehend the formula you provided! It looks like lots going in there!

Thanks to @AlexisOlson and to you for helping to come up with a solution for this. Much appreciated

[AlexisOlson]

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

[AliRade]

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

[AlexisOlson]

Try using "z" instead of "0" to right pad the LettersA and LettersB variables.

[AliRade]

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").

[AlexisOlson]

I would certainly expect "0" > "0A", just like "A" > "AA". Can you explain why you disagree?

[AliRade]

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.

[AlexisOlson · Accepted solution]

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
    Result

Note: The ?? -1 syntax replaces nulls with -1.

[AliRade]

Works perfectly 🙂 Thanks a lot. This saves me an extra set of coding!

Much appreciated 😀

[ronrsnfld]

You could split the column by transition from digit to non-digit. But you'll need some extra logic for those entries that begin with a letter.