topic Re: Table.SelectRows when one column concatenated and the other is split into rows in DAX Commands and Tips

Table.SelectRows when one column concatenated and the other is split into rows

user180618 — Mon, 05 Jun 2023 21:57:27 GMT

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!

Re: Table.SelectRows when one column concatenated and the other is split into rows

wdx223_Daniel — Tue, 06 Jun 2023 01:44:30 GMT

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),",")})

Re: Table.SelectRows when one column concatenated and the other is split into rows

user180618 — Wed, 07 Jun 2023 18:32:49 GMT

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"