Convert Spotfire calculated column to PowerBi

Question

I am converting a Spotfire dashboard to Power BI and am struggling on how to convert one of my calculated columns:

CASE  
WHEN MostCommon(If([Language]="English (UK)",[Training title])) OVER ([Internal Course Code]) IS NULL THEN MostCommon(If([Language]="Dutch (The Netherlands)",[Training title])) OVER ([Internal Course Code])
ELSE MostCommon(If([Language]="English (UK)",[Training title])) OVER ([Internal Course Code])
END

Situation: some courses are available in several languages. So the [Training title] differs but the content is the same, only the language differs. But the system sees it as different courses. In my visualizations I would like to group the courses together (irrespective of the language). This can be done with the column [Internal Course Code]. However, [Internal Course Code] is not meaningful to end users, so I would like to create a new column in which I include the [Training Title] next to the [Internal Course Code]. Since there are different titles for the Internal Course Codes I would like to show the (1) English Titles. In some cases there is no English title, then I would like to show the Dutch Title.

In Spotfire I used the OVER-function. Does anyone have a suggestion how to write this down in PowerBI?

Any help or guidance would be very much appreciated!

Sample data & desired output:

Internal Course Code	Training Title	Language	DESIRED OUTPUT
100	Optimism 101	English (UK)	Optimism 101
100	Optimisme voor beginners	Dutch (The Netherlands)	Optimism 101
100	Optimisme, mode d'emploi	French (France)	Optimism 101
139	Digitale transformatie	Dutch (The Netherlands)	Digitale transformatie
169	Prendre la parole en public	French (France)	Presentation Skills
169	Presentation Skills	English (UK)	Presentation Skills
169	Presenteren	Dutch (The Netherlands)	Presentation Skills
186	Glashelder leiderschap	Dutch (The Netherlands)	Glashelder leiderschap
81	Beïnvloeden	Dutch (The Netherlands)	Influencing
81	Influencer	French (France)	Influencing
81	Influencing	English (UK)	Influencing

Answer 1

Here you go. Make sure to change #"Changed Type" in the custom column to whatever the name of your previous step is.

Source table:

Result:

Custom column:

Custom column code:

let rows = 
Table.SelectRows(#"Changed Type", (x)=> x[Internal Course Code] = _[Internal Course Code] and (Text.StartsWith(x[Language], "English") or Text.StartsWith(x[Language], "Dutch"))), 
sorted = Table.Sort(rows, {"Language", Order.Descending})

in sorted[Training Title]{0}

Full query

  let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZFBS8QwEIX/ytCTQg+twrpeRVdEUEE9rXvINm/bwemkJOn+LX+Ef8y0orBo6Z5CmPe9Ny9Zr7OyKCjLs8cucsuhpbIoh/uN1sKhoZPX+1PKNvkfIWjvnKctalaFD8Psuo9VQl4a0ANiAy9GbZjgc2qdBdm3vijOLtB24ngQrDx0cFl5oxV+4PPLMYBrjkZAMQ3DzvnWRMZ89GKkn5Kz9SAx1Bnvkg2Uun4rXE0H/6IBGlOaU3p+Z5Ew8UoHeiTL+e2Wi0FzKyY0EAtPAk5HqBrTzdLL8buu8Pmhe3GwRwR+I3e6kz41TnlT5Q+FrPU/nTdf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Internal Course Code" = _t, #"Training Title" = _t, Language = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Internal Course Code", Int64.Type}, {"Training Title", type text}, {"Language", type text}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let rows = 
            Table.SelectRows(#"Changed Type", (x)=> x[Internal Course Code] = _[Internal Course Code] and (Text.StartsWith(x[Language], "English") or Text.StartsWith(x[Language], "Dutch"))), 
            sorted = Table.Sort(rows, {"Language", Order.Descending})
    
            in sorted[Training Title]{0})
    in
        #"Added Custom"