topic Re: How to take Max String value based on Another Column which contains same values. in Power Query

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

KR300 — Tue, 10 Dec 2024 13:17:16 GMT

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

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

Ahmedx — Tue, 10 Dec 2024 13:47:22 GMT

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"

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

ronrsnfld — Tue, 10 Dec 2024 14:01:02 GMT

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"

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

ThxAlot — Wed, 11 Dec 2024 12:27:51 GMT

.......

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

ronrsnfld — Tue, 10 Dec 2024 20:14:44 GMT

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

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

KR300 — Wed, 11 Dec 2024 08:30:17 GMT

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

I need more help on this & how it can be done.
If Issue ID is same, I want only one Issue ID with Max FixVersion Record
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.., )

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

KR300 — Wed, 11 Dec 2024 09:53:50 GMT

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

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

Chewdata — Wed, 11 Dec 2024 12:42:39 GMT

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:

use a custom function to create a numeric combined value from main and sub version.
Group the rows on issue to get the max
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!

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

ronrsnfld — Wed, 11 Dec 2024 13:12:15 GMT

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

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

Anonymous — Thu, 12 Dec 2024 02:03:16 GMT

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.

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

KR300 — Thu, 12 Dec 2024 06:04:27 GMT

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

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

Chewdata — Thu, 12 Dec 2024 10:57:31 GMT

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.

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

AlienSx — Thu, 12 Dec 2024 13:12:12 GMT

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

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

ronrsnfld — Thu, 12 Dec 2024 13:14:54 GMT

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.

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

KR300 — Tue, 17 Dec 2024 10:30:46 GMT

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

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

KR300 — Tue, 17 Dec 2024 10:33:45 GMT

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

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

ronrsnfld — Tue, 17 Dec 2024 13:34:57 GMT

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: