Search code examples
powerbipowerquerym

Index Column With Exception - POWER BI / DAX / M

I have a column with unique Values of Countries, I just need to to create a index column.

The problem is that in this column I have a "country" that is "Other", I need this country to appears at the end.

let
    Source = Table.SelectColumns(Orders,"Country"),
    #"Removed Duplicates" = Table.Distinct(Source),
    #"Add Index" = Table.AddIndexColumn(#"Removed Duplicates", "CountryId", 1, 1, Int64.Type)
in
    #"Add Index"

"Add index but when/with Country="Other" put at the final.

Thanks you

Solution

  • The simplest way I can think of would be to add a new column that replaces the index for "Other" (and then delete the old index column).

    Table.AddColumn(#"Add Index", "NewIndex", each
    if [Country] = "Other" then List.Max(#"Add Index"[CountryId]) + 1 else [CountryId]
)