CODEWITHSUNDEEP
powerbidaxpowerquerypowerbi-desktop
Project Insights
Project Insights

Reputation: 108

Custom Column in Power BI

Can someone please help me with custom column code in Power BI Query Editor. I wish to get a value "Latest" in custom column against "CRTG-0006" as 0006 is the highest number in the column. CRTG-0006 is the latest version of the Program CRTG & CRTG-0001 being the first version of the Program. In future, let's say if I add "CRTG-0007" file in the root folder, Custom Column return value shall be "Latest" for "CRTG-0007" and shall be "null" for "CRTG-0006" and all previous versions of the Program.

enter image description here

Upvotes: 0

Views: 197

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

Given your layout,

  • Assume your program and versions column header is Program
  • Assume your preceding step is #"Changed Step
  • Then, in the Add Custom Column dialog you can use this formula:
if [Program] = List.Max(#"Changed Type"[Program]) then "Latest" else null

enter image description here

This works because your format is such that simple alphabetical sorting will work. The program name/abbreviation is always the same, and the version number is padded with zero's

By the way, if you have multiple programs in the Program column, I would group by the program base name, and apply the same algorithm in a custom aggregation.

Upvotes: 1

horseyride
horseyride

Reputation: 21298

It's a bit hard to tell what you want but you can right click then split the column on - to get the number. You can then convert type to numerical and sort or take the List.Max([ColumnName])

Upvotes: 1

Related Questions