Merging Rows With Share Unique Values

[stuartbrown]

Hi,

I've been lurking around the forum for a while learning lots from you and have gone through a number of the tutorials provided :).  I have an issue that I just can't seem to resolve however and I'm hoping someone can help me.

I pull data from the Jira API and this returns a record of tickets.  Each ticket can have multiple 'component' values, and also multiple 'label' values.  The data in PowerBI shows a row for each combination of these (truncated example below)

Ticket URL	Ticket Number	Labels	Components
url/123	ABC-123	Label 1	Component 1
url/123	ABC-123	Label 1	Component 2
url/321	ABC-321	Label 2	Component 1
url/321	ABC-321	Label 3	Component 2
url/321	ABC-321	Label 1	Component 1
url/222	ABC-222	Label 2	Component 1

I would like to have only one row for each Ticket Number and combine the various labels into  comma-separated values in other columns.

Ticket URL	Ticket Number	Labels Array	Components Array
url/123	ABC-123	Label 1	Component 1, Component 2
url/321	ABC-321	Label 1, Label 2, Label 3	Component 1, Component 2

I have also shared some real data in Excel and pbix format - in these you will see that there are a number of other columns that I need to keep.  If anyone can help I'd greatly apprecate it!

[Ashish_Mathur]

Hi,

Try this M code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKi3K0Tc0MlbSUXJ0ctaFsHwSk1JzFAyBLOf83IL8vNS8EiAvVod41UZw1cZGhlDVEBZEtREOs7GrNibJbFzuNjIygqqGsHC4JBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Ticket URL" = _t, #"Ticket Number" = _t, Labels = _t, Components = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Ticket URL"}, {{"All labels", each Text.Combine(List.Distinct([Labels]), ", "), type text}}),
    Joined = Table.Join(Source, "Ticket URL", #"Grouped Rows", "Ticket URL"),
    #"Grouped Rows1" = Table.Group(Joined, {"Ticket URL"}, {{"All components", each Text.Combine(List.Distinct([Components]), ", "), type text}}),
    Joined1 = Table.Join(Joined, "Ticket URL", #"Grouped Rows1", "Ticket URL"),
    #"Removed Columns" = Table.RemoveColumns(Joined1,{"Labels", "Components"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns")
in
    #"Removed Duplicates"

Hope this helps.

[mahoneypat]

Thank you for providing the pbix.  That makes is easier to help.  This is more easily done in query, so I copied your final table (since I couldn't refresh against your source) and put it into a new query to demonstrate.  You can see how to do it with the M code below and do the same steps in your original query.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.  The key steps are to group on your ticket number column but keep all rows, and then use list functions and text.combine to make the string of values.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("zdddb4IwFAbgv0K4nZae1vLRuznNErMPM9iV8QJnN4mTGqju76/qptkcxCEICYECITw5b3MKo5E5U2qZcst6lRJNwgTFQlmJSJUVLiPrPVSbYZSmK2EBBQ8cs2X2h36bUpvpYXf1trug9wQT3AbcJhCAzTHllCDPgSsMGOvb92IarRZ6sNsCafTk7lmje/1sjlvNsNz0/f9bXAp7S2fzgiBM58cYHECHU4dTijxMvzF38kPvh4mc6sNAzmJ96MlYnFOcEkC+SNbRi3jYnpTEYtjes0hWZsA2mTGXA0Gu6x5l9lWpMuZQhZ5C86hCz+NTtybPj4nUmNTyVPVll6cqlCDpsL0Ke1kqEgDmTPcBG2Gb/NmYygiuKkxNlSl9YldLqqlKUaxEsoiUErFqTJ3yUQUrRQ8oNx8FnGHkgJPVuwdSGL7YrrhibZy76l5EVqhnXkR2m4RTMZFyXpPv14dU45I9zVdfvqf5iqeMXeYdlE6GEnsBUE5A/0wggCq/Sqv0FEmxSs+2044/AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Ticket URL" = _t, #"Ticket Number" = _t, #"Ticket Type" = _t, #"JIRA Project (?)" = _t, #"Created Date" = _t, Priority = _t, Labels = _t, Assignee = _t, Status = _t, Components = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Ticket URL", type text}, {"Ticket Number", type text}, {"Ticket Type", type text}, {"JIRA Project (?)", type text}, {"Created Date", type datetimezone}, {"Priority", type text}, {"Labels", type text}, {"Assignee", type text}, {"Status", type text}, {"Components", type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Ticket Number", "Labels", "Components"}),
    #"Grouped Rows" = Table.Group(#"Removed Other Columns", {"Ticket Number"}, {{"AllRows", each _, type table [Ticket Number=nullable text, Labels=nullable text, Components=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Labels", each Text.Combine(List.Distinct([AllRows][Labels]), ", ")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Components", each Text.Combine(List.Distinct([AllRows][Components]), ", ")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"AllRows"})
in
    #"Removed Columns"

Regards,

Pat

[stuartbrown]

Many thanks for your answer!  It works very well for the output table format I showed in the question.  Do you know how I could also keep the other columns in the data (assignee, status etc)?

[Ashish_Mathur]

Hi,

Whom are you replying to?  If it is me you are replying to then share the other columns and show the exact result.

[stuartbrown]

@Ashish_Mathur sorry I hit reply below your reply but it seems not to have associated the two, sorry my fault 🙂

The full list of columns is below, those in bold are the columns that are modified in your solution, the others do not need to be modified but need to still be present in the output.  There is a pbix and excel version of the real data at the end of the orginal question if that helps too?

Ticket URL
Ticket Number
Ticket Type
JIRA Project (?)
Created Date
Priority
Labels
Assignee
Status
Components
Summary

[Ashish_Mathur]

Hi,

This M code seems to work

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Ticket URL"}, {{"All labels", each Text.Combine(List.Distinct([Labels]), ", "), type text}}),
    Joined = Table.Join(Source, "Ticket URL", #"Grouped Rows", "Ticket URL"),
    #"Grouped Rows1" = Table.Group(Joined, {"Ticket URL"}, {{"All components", each Text.Combine(List.Distinct([Components]), ", "), type text}}),
    Joined1 = Table.Join(Joined, "Ticket URL", #"Grouped Rows1", "Ticket URL"),
    #"Removed Columns" = Table.RemoveColumns(Joined1,{"Labels", "Components"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns")
in
    #"Removed Duplicates"

Hope this helps.

[stuartbrown]

@Ashish_Mathur thanks - I tried that but get an error `Expression.Error: We couldn't find an Excel table named 'Data'.`.  I tried swapping the source from the latest query with the source from your previous query but that just gave the same limited columns as earlier.

Is it that I need to list out all the columns here:

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKi3K0Tc0MlbSUXJ0ctaFsHwSk1JzFAyBLOf83IL8vNS8EiAvVod41UZw1cZGhlDVEBZEtREOs7GrNibJbFzuNjIygqqGsHC4JBYA", BinaryEncoding.Base64), Compression.Deflate)),

let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Ticket URL" = _t, #"Ticket Number" = _t, Labels = _t, Components = _t]),

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[stuartbrown]

@Ashish_Mathur many thanks for your ongoing help.  When I run your file I get a duplicate of ticket 3340 - do you? 

Also (and sorry if this is a really newbie question) - how do I correctly modify the source to point to my Query

Running this gives me an error `Expression.Error: The name '_t' wasn't recognized. Make sure it's spelled correctly.` at the source stage and at change type stage and error `Expression.Error: We cannot convert a value of type Table to type List.
Details:
Value=[Table]
Type=[Type]`

[Ashish_Mathur]

Hi,

You are welcome.  the 334o rows appears twice because of different entries in the Summary column.  Once you make them the same, the duplicate row vanishes.  See the image

[stuartbrown]

@Ashish_Mathur many thanks!  And can you advise how to swap the source to the query shown in the image?

[Ashish_Mathur]

Just replace the name there.

[stuartbrown]

OK,  so from the PBIX you provided the source is:

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("1dVPb4IwGAbwr9JwncDbliJwm3+ybNnUCDsZD81stBkWA2Xbx1/Rzc2o4bAtqxf6AqH55cmTMps5K603VeL7Rb3hS5kLj+ucV5XkylNC+6WotM830s+5bkZZVbXwMcUx7jodZzhJXUpDZsZevdw9MFcCBFwMLsEZDhOgCSVe3MVXgAHM6wexkPXaDKrO868lK9Cg2O2BetePZppMxwPkogl1EbophVBIizeNnHnHWnh/mP4RPKJ4Dw8aTcar52M5ZDhIaDeh1IuBfsrvi9eGVRYLs9wVK2WWQaHEQdy3oz6GGCAg/y5NRfkin8Roe9PqpT/iMgj3XHKuEZg1jWBRgokXRdFRIz6iTXnJVye73GCx+ch8ZjN2198LwY6nPcuwB729jDK0ku2rRCv5V4tBArYnQ3yOTDIMCTOnWehBSE6eu2f/c1soMLO9tdBvidoAPWiA/bm2cS1LVyotyrXUWih9Efm2g48Tnr8D", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Ticket URL" = _t, #"Ticket Number" = _t, #"Ticket Type" = _t, #"JIRA Project (?)" = _t, #"Created Date" = _t, Priority = _t, Labels = _t, Assignee = _t, Status = _t, Components = _t, Summary = _t]),

Based on this and the documentation I have modified to:

Source = Table.FromRows((#"EPS BAU (2)"),{#"Ticket URL" = _t}, {#"Ticket Number" = _t}, {#"Ticket Type" = _t}, {#"JIRA Project (?)" = _t}, {#"Created Date" = _t}, {Priority = _t}, {Labels = _t}, {Assignee = _t}, {Status = _t}, {Components = _t}, {Summary = _t}) as table ),

But I get an syntax error 'Token, comma expected' at `as table),`

My understanding of the docs is that th FromRows function takes a list (assume array?) but I can't find the way to get this list from `EPS BAU (2)`

 

[Ashish_Mathur]

Hi,

I don't think i can help you beyond this.  One last try would be to copy all the lines (after the Source code line) in the M code of my solution and paste them after the source line of the EPS BAU (2) table.  The spellings (including upper/lower case of headings in EPS BAU (2) tabel should be the same as in my table).

Hope this helps.