Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to 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"
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"
@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"
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. (=
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |