Concatenating/Grouping row values in table with many to one relationship

[Anonymous]

I have two related tables Labels and Issues. The relationshp is Many to One.

Many(Labels) to One(Issue)

However not all Issues have Labels.

I want to create another table that is 1:1 with Issue

One(New Table) to One(Issue) 

And have two columns:

the Key from Issue,

and the second column: (NewConcatenatedColumn)

That has, concatenated text Labels[LabelText] for rows with the same Label Key/Issue Key.

I believe I have successfully started this endeavor with creating a new table, with distinct value from the Key of the Issues Table.

MyNewTable = DISTINCT(Issues[Key])

Can anyone help me with labeling my issues, so I no longer have issues?

Thanks in advance.

See screenshot below.

[v-juanli-msft · Accepted solution]

Hi

What is your goal for creating reports?

We need to know the goal so to define how to create the relationships.

If you just need to create a new table as you said,

Open edit queries->Advanced editor, add code (blue)

let
   Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUapSitWJVjICsirALGMgKxnMMgGyysAsUyArCcwyA7LywCxzICsXzAKZkgU3JRtuSiHclHKl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Key = _t, #"label text" = _t]),
   #"Changed Type" = Table.TransformColumnTypes(Source,{{"Key", Int64.Type}}),
   Source2 = Table.NestedJoin(Issue, {"Key"}, #"Changed Type", {"Key"}, "Labels", JoinKind.LeftOuter),
   #"Expanded Labels" = Table.ExpandTableColumn(Source2, "Labels", {"Key"}, {"Labels.Key"}),
   #"Filtered Rows" = Table.SelectRows(#"Expanded Labels", each ([Labels.Key] = null)),
   #"Removed Columns" = Table.SelectColumns(#"Filtered Rows",{"Key"}),
   Bew=Table.Combine({#"Changed Type", #"Removed Columns"})
in
   Bew

Close &&apply

Then create a new table

Table =
SUMMARIZE (
   ADDCOLUMNS (
       Labels,
       "new label text", CONCATENATEX ( FILTER ( Labels, [key] = EARLIER ( [key] ) ), [label text], "," )
   ),
   [key],
   [new label text]
)

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.