How to create Custom_list = ["x", "Y"] in advanced Editor in added Column

[Anonymous]

Good morning,

I am trying to build a custom column in List in Powerbi.

The given column is filled with [Countries] for which they should an Output "Region".

This is the code at the moment.

#"Hinzugefügte bedingte Spalte" = Table.AddColumn(Tabelle2_Table, "Region", each if [#"Country"] = "Canada" or [#" Country"] = "USA" then "AM" else if [#"Country"] = "Sweden" then "EMEA" else if [#"Country"] = "Japan" then "AP" else "Not Defined")

As you see for "AM" i already used an or function to add a second country for the Output "AM".

Is there an elegant solution for example making a predestined list in the custom editor like in Python:

Country_AM = ["USA", "Canada", "Brazil", "..."] and then refer to it like:

#"Hinzugefügte bedingte Spalte" = Table.AddColumn(Tabelle2_Table, "Region", each if [#"Country"] == Country_AM then "AM"

Or do i have to make hundreds of else if-es? I did not archieve something with

>#"Hinzugefügte bedingte Spalte" = Table.AddColumn(Tabelle2_Table, "Region", each if [#"Country"] = ("Canada", "USA", "...") then "AM"

I do not want to import a seperate excel file, i want the pure code for later on to copy in other PowerBi's.

Sadly google is not on my side today, i might have overlooked something but cannot find it...

Thanks for your help in advance!

Have a nice week!

[HotChilli · Accepted solution]

You can create a list in code :

countryList = { "uk", "germany"}

or use Enter Data and make a list through the interface.

You use List.Contains to test membership, for example 

each if List.Contains(countryList, [Column1]) then "AM" else "PM"

[Anonymous]

@HotChilliI sadly ran into a problem.

Expression.Error: Der Feldzugriff kann nicht auf den Typ "List" angewendet werden.  !!#Translates to: The field access cannot be used onto the type "List".!!
Details:
Value=[List]
Key=SYS Country|Key Figures

X = anonymized

It works fine for the "AM", but the others are Errors

let
Quelle = X.Files("X", [ApiVersion = X]),
#"X" = Quelle{[Name="X",#"Folder Path"="X"]}[X],
#"Importierte Excel-Datei" = Excel.Workbook(#"X xlsx_https://X/"),
Tabelle2_Table = #"Importierte Excel-Datei"{[Item="Tabelle2",Kind="Table"]}[Data],

Region_Countries_AM = {"Canada","USA","Guam","..."},

Region_Countries_EMEA = {"Sweden","Denmark","Finland","..."},
Region_Countries_AP = {"Japan","Singapore","Indonesia","..."},

#"Hinzugefügte bedingte Spalte" = Table.AddColumn(Tabelle2_Table, "Region", each if List.Contains(Region_Countries_AM, [#"SYS Country|Key Figures"]) then "AM" else if List.Contains(Region_Countries_EMEA[#"SYS Country|Key Figures"]) then "EMEA" else if List.Contains(Region_Countries_AP, [#"SYS Country|Key Figures"]) then "AP" else "Other")
in
#"Hinzugefügte bedingte Spalte"

[Anonymous]

Thank you soooo MUCH!

This is exactly what i was looking for.

I tried it with 1 list and now trying it with seperate lists and outputs. If i will get another problem i will keep you updated. (=