How to take Max String value based on Another Column which contains same values.

[KR300]

Hi Team,

Here based on the Isssue Id column we need to fetch max value of Fixversion and remove Other records.

Description: Issue ID - 2 is having multiple FixVersions ( SFP.R.11.7, SFP.R.11.8 & SFP.R.11.9 ), but i want to fetch only one record for each Issue ID with Max FixVersion like below

[Chewdata · Accepted solution]

Hey!

You can try an variation on this code. Som additional code may be needed to clean your data before.

The code below code does the following:

1. use a custom function to create a numeric combined value from main and sub version.
2. Group the rows on issue to get the max
3. Filter the rows so only the value that equals max will remain. 

let
    Source = YOURDATA,
    filterEmpty = Table.SelectRows(Source, each [FixVersion] <> null and [FixVersion] <> ""),

    fn_VersionCombined = (vVersion as text) =>
    let
        split = Text.Split(vVersion, "."),
        main = split{2},
        sub = split{3},
        outcome  = Number.From(Text.Combine({main, if Number.From(sub) <= 9 then "0" & sub else sub}))
    in
        outcome,
    
    invoke_fnVersionCombined = Table.AddColumn(filterEmpty, "VersionCombined", each fn_VersionCombined([FixVersion]), Int64.Type),
    GroupRows = Table.Group(invoke_fnVersionCombined, {"Issue ID"}, {{"Max", each List.Max([VersionCombined]), type number}, {"Table", each _, type table [Issue ID=nullable number, FixVersion=nullable text, VersionCombined=number]}}),
    ExpandTable = Table.ExpandTableColumn(GroupRows, "Table", {"FixVersion", "VersionCombined"}, {"FixVersion", "VersionCombined"}),
    FilterRows = Table.SelectRows(ExpandTable, each ([VersionCombined] = [Max]))
    
in
    FilterRows

Hopefully this is usefull, if so consider accepting it as a solution to help other users!

Good luck!

[ronrsnfld · Accepted solution]

The code I supplied earlier seems to work with your new sample:

Source

Results

By the way, if you want to change the name of the resultant column, merely edit the last line of code before the "in":

Change:

Table.ExpandRecordColumn(#"Grouped Rows", "Latest Version", {"FixVersion"})

to something like:

Table.ExpandRecordColumn(#"Grouped Rows", "Latest Version", {"FixVersion"},{"MaxVersion"})

[v-xinruzhu-msft · Accepted solution]

Hi,

Thanks for the solution KR300 , Chewdata , ThxAlot  and Ahmedx  offered, and i want to offer some more information for user to refer to.

hello @KR300 , you can refer to the follwing code in advanced editor in power query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdBLCsAgDATQu2QtwbEf9QJdF7sU73+NfrDUMtk+EoaZWgXi5Nh2LQpokuaqhJHiQIWviu435YemkSJTYjIe4R+b+XNhWpkiU2LKTFcwGwwLhhlFYVTA1yH38j/q5QeDVxgWyN7JYcz0N8rtq7cT", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Issue ID" = _t, FixVersion = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Issue ID", Int64.Type}, {"FixVersion", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Select([FixVersion],{"0".."9"})),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"Issue ID"}, {{"Max", each let a=List.Max([Custom])
in List.Select([FixVersion],each Text.Contains(Text.Remove(_,"."),Text.From(a))){0}, type nullable text}})
in
    #"Grouped Rows"

Ouptut

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[KR300]

Hi All, A lot of thanks for everyone. I will try what is the best way of workaround from all above options & Reply to all

Thanks 

[AlienSx · Accepted solution]

let
    result = Table.Group(
        source_table, 
        "Issue ID", 
        {
            "FixVersion", (x) => List.Max(
                x[FixVersion], 
                null, 
                (w) => ((nums) => Number.From(nums{0}) * 1000 + 
                    Number.From(nums{1})
                )(List.LastN(Text.Split(w, "."), 2))
            )
        }
    )
in
    result

[v-xinruzhu-msft · Accepted solution]

Hi,

Thanks for the solution KR300 , Chewdata , ThxAlot  and Ahmedx  offered, and i want to offer some more information for user to refer to.

hello @KR300 , you can refer to the follwing code in advanced editor in power query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdBLCsAgDATQu2QtwbEf9QJdF7sU73+NfrDUMtk+EoaZWgXi5Nh2LQpokuaqhJHiQIWviu435YemkSJTYjIe4R+b+XNhWpkiU2LKTFcwGwwLhhlFYVTA1yH38j/q5QeDVxgWyN7JYcz0N8rtq7cT", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Issue ID" = _t, FixVersion = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Issue ID", Int64.Type}, {"FixVersion", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Select([FixVersion],{"0".."9"})),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"Issue ID"}, {{"Max", each let a=List.Max([Custom])
in List.Select([FixVersion],each Text.Contains(Text.Remove(_,"."),Text.From(a))){0}, type nullable text}})
in
    #"Grouped Rows"

Ouptut

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Chewdata · Accepted solution]

Hey!

You can try an variation on this code. Som additional code may be needed to clean your data before.

The code below code does the following:

1. use a custom function to create a numeric combined value from main and sub version.
2. Group the rows on issue to get the max
3. Filter the rows so only the value that equals max will remain. 

let
    Source = YOURDATA,
    filterEmpty = Table.SelectRows(Source, each [FixVersion] <> null and [FixVersion] <> ""),

    fn_VersionCombined = (vVersion as text) =>
    let
        split = Text.Split(vVersion, "."),
        main = split{2},
        sub = split{3},
        outcome  = Number.From(Text.Combine({main, if Number.From(sub) <= 9 then "0" & sub else sub}))
    in
        outcome,
    
    invoke_fnVersionCombined = Table.AddColumn(filterEmpty, "VersionCombined", each fn_VersionCombined([FixVersion]), Int64.Type),
    GroupRows = Table.Group(invoke_fnVersionCombined, {"Issue ID"}, {{"Max", each List.Max([VersionCombined]), type number}, {"Table", each _, type table [Issue ID=nullable number, FixVersion=nullable text, VersionCombined=number]}}),
    ExpandTable = Table.ExpandTableColumn(GroupRows, "Table", {"FixVersion", "VersionCombined"}, {"FixVersion", "VersionCombined"}),
    FilterRows = Table.SelectRows(ExpandTable, each ([VersionCombined] = [Max]))
    
in
    FilterRows

Hopefully this is usefull, if so consider accepting it as a solution to help other users!

Good luck!

[KR300]

I have entere some more data. How to do in this case

[ronrsnfld · Accepted solution]

The code I supplied earlier seems to work with your new sample:

Source

Results

By the way, if you want to change the name of the resultant column, merely edit the last line of code before the "in":

Change:

Table.ExpandRecordColumn(#"Grouped Rows", "Latest Version", {"FixVersion"})

to something like:

Table.ExpandRecordColumn(#"Grouped Rows", "Latest Version", {"FixVersion"},{"MaxVersion"})

[KR300]

But Power BI Consider SFP.R.8.9 as max string Compared to SFP.R.11.8 ( but this is the newest version right and Max Stiring). See Key -> SMAR-2363

[ronrsnfld]

I don't understand your two comments.

Version SFP.R.11.8  is a later version than SFP.R.8.9 and my code reports it as such.

Also, in your example, there is no column labeled "Key" and no entry SMAR-2363 so I have no idea what you are referring to.

Please clarify both points.

[KR300]

Order of the FixVersion as shown in the below

SFP.R.8.1 ( starting )

SFP.R.8.2

..

..

.

SFP.R.8.10

SFP.R.8.11

SFP.R.9.1

SFP.R.9.2

..

..

SFP.R.9.10

SFP.R.9.11

Silimary

....

SFP.R.11.8

SFP.R.11.9

SFP.R.11.10

SFP.R.11.11 ( Latest )

Key/Issue ID /ID            FixVersion

SMAR-2363                  SFP.R.11.8 

SMAR-2363                  SFP.R.8.9

SMAR-2363                  SFP.R.9.9

But,for the SMAR-2363 Power BI is taking Maxstring SFP.R.9.9 Actually the correct/latest one is SFP.R.11.8

( i think based on ordering of the values 9 > 1 highlighted , so taking SFP.R.9.9 for the SMAR-2363 - but this is wrong ).

[ronrsnfld]

I cannot reproduce your issue with the code I have provided and the examples you have provided. As you can see, the results show SMAR-2363  -  SFP.R.11.8 which you have noted is correct:

Version Table:

Issue IDFixVersion

1	SFP.R.11.8
2	SFP.R.11.7
2	SFP.R.11.8
2	SFP.R.11.9
3	SFP.R.11.7
3	SFP.R.11.8
3	SFP.R.11.9
3	SFP.R.11.10
4	SFP.R.11.8
5	SFP.R.11.8
6	SFP.R.11.8
7	SFP.R.11.8
8	SFP.R.11.8
9	SFP.R.11.8
10	SFP.R.11.8
11	SFP.R.11.8
12	SFP.R.11.8
13	SFP.R.11.8
14	SFP.R.11.8
15	SFP.R.9.9
15	SFP.R.9.10
15	SFP.R.10.1
15	SFP.R.10.2
15	SFP.R.11.7
15	SFP.R.11.8
15	SFP.R.11.9
15	SFP.R.8.9
SMAR-2363	SFP.R.11.8
SMAR-2363	SFP.R.8.9
SMAR-2363	SFP.R.9.9

Code: (altered only to change version column name to `MaxVersion`, and Issue ID column type to `any`)

let
    Source = VersionTable,

//Split digit and letter parts
    #"Split" = Table.AddColumn(Source, "SplitParts", each
        let 
            split = Text.Split([FixVersion],"."),
            letter = Text.Combine(List.Range(split,0,2),"."),
            digits = Number.From(split{2})*1000 + Number.From(split{3})
        in 
            [letters=letter, digits=digits],
            type [letters=text, digits=Int64.Type]),
   
    #"Expanded SplitParts" = Table.ExpandRecordColumn(Split, "SplitParts", {"letters", "digits"}, {"letters", "digits"}),
    #"Grouped Rows" = Table.Group(#"Expanded SplitParts", {"Issue ID"}, {
        
        {"Latest Version", each Table.Last(Table.Sort(_, {{"letters", Order.Ascending}, {"digits", Order.Ascending}}))
        
        
        , type [Issue ID=any, FixVersion=text, letters=text, digits=number]}}),
    #"Expanded Latest Version" = Table.ExpandRecordColumn(#"Grouped Rows", "Latest Version", {"FixVersion"},{"MaxVersion"})
in
    #"Expanded Latest Version"

Results:

[ThxAlot]

.......

[ronrsnfld]

With your algorithm, if I add  SFP.R.10.22 to ID3, it gets returned as the latest version vs SFP.R.11.10

[ronrsnfld]

• Split the version into the letters and digits portion
• Split the digits portion by the dot, then combine them to create a whole number
• Group by Issue ID
• Sort each group by letter and digits
• Return the last value of each subgroup

let
    Source = Table.FromColumns({
        {1,2,2,2,3,3,3,3},
        {"SFP.R.11.8","SFP.R.11.7","SFP.R.11.8","SFP.R.11.9","SFP.R.11.7","SFP.R.11.8","SFP.R.11.9","SFP.R.11.10"}},
                type table[Issue ID=Int64.Type, FixVersion=text]),

//Split digit and letter parts
    #"Split" = Table.AddColumn(Source, "SplitParts", each
        let 
            split = Text.Split([FixVersion],"."),
            letter = Text.Combine(List.Range(split,0,2),"."),
            digits = Number.From(split{2})*1000 + Number.From(split{3})
        in 
            [letters=letter, digits=digits],
            type [letters=text, digits=Int64.Type]),
   
    #"Expanded SplitParts" = Table.ExpandRecordColumn(Split, "SplitParts", {"letters", "digits"}, {"letters", "digits"}),
    #"Grouped Rows" = Table.Group(#"Expanded SplitParts", {"Issue ID"}, {
        
        {"Latest Version", each Table.Last(Table.Sort(_, {{"letters", Order.Ascending}, {"digits", Order.Ascending}}))
        
        
        , type [Issue ID=number, FixVersion=text, letters=text, digits=number]}}),
    #"Expanded Latest Version" = Table.ExpandRecordColumn(#"Grouped Rows", "Latest Version", {"FixVersion"})
in
    #"Expanded Latest Version"

[Ahmedx]

pls try this code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQp2C9AL0jM01LNQitWJVjJCFjLHFMKiyhIsZIyp0RhTozEBjYYGSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Issue ID" = _t, FixVersion = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Issue ID", Int64.Type}, {"FixVersion", type text}}),
    #"Removed Duplicates" = Table.Distinct(#"Changed Type", {"FixVersion"}),
    #"Grouped Rows" = Table.Group(#"Removed Duplicates", {"Issue ID"}, {{"Count", each List.Max([FixVersion]), type nullable text}})
in
    #"Grouped Rows"

[KR300]

Hi it helps. But If we have some more data like in the screenshot. Pls have a look.

1. I need more help on this & how it can be done.
2. If Issue ID is same, I want only one Issue ID with Max FixVersion Record
3. Note: FixVersion from Least to Highest ( 8.1,8.2,....,8.10,8.11, 8.12..,   11.1, 11.2,....,11.10, 11.11, 11.12.., )
4. 

[Chewdata]

Hey,
Have you tried my solution yet? It should do just that. Same as the other solutions. Otherwise the problem you are facing is not yet clear to us.