Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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!

 

 

1 ACCEPTED SOLUTION
HotChilli
Super User
Super User

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"

 

 

View solution in original post

3 REPLIES 3
HotChilli
Super User
Super User

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
Not applicable

@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
Not applicable

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. (=

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.