Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I want to create a custom column using Table.SelectRows (or any other expression) that does the following. In my Table1, I have a column called Cities that lists values separated by a comma:
Country | Cities |
France | Paris, Lyon, Marseille |
Germany | Berlin, Munich, Dortmund |
In my Table2, I have a Sister_Cities column like this:
City | Sister_Cities |
Paris | Rome |
Lyon | Birmingham |
Lyon | Guangzhou |
Lyon | Montreal |
Lyon | Addis Ababa |
Marseille | Genoa |
Marseille | Glasgow |
I know the logical thing to do would be to split the first table into rows and then do a join based on the Cities and City columns, but I want to keep them concatenated so that I have a new column in Table1 that looks like this:
Country | Cities | Custom.Sister_Cities |
France | Paris, Lyon, Marseille | Rome, Birmingham, Guangzhou, Montreal, Addis Ababa, Genoa, Glasgow |
What's the best way to achieve this? Appreciate any advice!
NewStep=let a=Table.Group(Table2,"City",{"n",each [Sister_Cities]}) in Table.TransformColumns(Table1,{"Cities",each Text.Combine({_}&List.TransformMany(Text.Split(_,","),each a{[City=_]}?[n]? ??{},(x,y)=>y),",")})
This gives a "Expression.Error: A cyclic reference was encountered during evaluation" error.
Here is the full code in Advanced Editor:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcitKzEtOVdJRCkgsyizWUfCpzM/TUfBNLCpOzczJSVWK1YlWck8tyk3MqwQqckotyskEyZfmZSZn6Ci45BeV5JbmpSjFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Country = _t, Cities = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Country", type text}, {"Cities", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "NewStep", each let a=Table.Group(Table2,"City",{"n",each [Sister_Cities]}) in Table.TransformColumns(Table1,{"Cities",each Text.Combine({_}&List.TransformMany(Text.Split(_,","),each a{[City=_]}?[n]? ??{},(x,y)=>y),",")}))
in
#"Added Custom"