Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have 2 tables:
t1) with country_name (col1) and country_code (col2)
t2) with country_name
I want to add a new column in t2 containing country_code of t1 based on the value in country_name from t2.
How can I do it?
Solved! Go to Solution.
Try this
= try Foglio1[#"Code Country"]{List.PositionOf(Foglio1[#"Name Country"],[#"Country/Region"])} otherwise null
It gives the result for France. For other countries, they are missing in Froglio1, hence giving null.
In a custom column in t2, use below formula
= try t1{[country_name=[country_name]]}[country_code] otherwise null
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcitKzEtOVYrViVbyzEvJTFSKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [country_name = _t]),
#"Added Custom" = Table.AddColumn(Source, "Custom", each try t1{[country_name=[country_name]]}[country_code] otherwise null, type text)
in
#"Added Custom"
You can also use merge
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcitKzEtOVYrViVbyzEvJTFSKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [country_name = _t]),
#"Merged Queries" = Table.NestedJoin(Source, {"country_name"}, t1, {"country_name"}, "t1", JoinKind.LeftOuter),
#"Expanded t1" = Table.ExpandTableColumn(#"Merged Queries", "t1", {"country_code"}, {"country_code"})
in
#"Expanded t1"
it does not work, the condition always fail
Download this Excel where it is demonstrated that code is working.
https://1drv.ms/x/s!Akd5y6ruJhvhuhTQFpr4zilBbqsl?e=Dmp1Li
Let me know what error you are getting.
I am not getting error but the new column is populted with null in every row.
this is my t2:
and this is my t1:
the formula i used is:
try Foglio1{[#"Name Country"=[#"Country/Region"]]}[#"Code Country"] otherwise null
and it gaves me always null
Try this
= try Foglio1[#"Code Country"]{List.PositionOf(Foglio1[#"Name Country"],[#"Country/Region"])} otherwise null
still null
I tried to print the index but it is always -1 like the country is never found
Is it possible for you to share some sample data either in Excel or pbix format both t1 and t2?
It gives the result for France. For other countries, they are missing in Froglio1, hence giving null.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
101 | |
49 | |
19 | |
12 | |
11 |