cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

Merging Rows With Share Unique Values

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 URLTicket NumberLabelsComponents
url/123ABC-123Label 1Component 1
url/123ABC-123Label 1Component 2
url/321ABC-321Label 2Component 1
url/321ABC-321Label 3Component 2
url/321ABC-321Label 1Component 1
url/222ABC-222Label 2Component 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 URLTicket NumberLabels ArrayComponents Array
url/123ABC-123Label 1Component 1, Component 2
url/321ABC-321Label 1, Label 2, Label 3Component 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!

14 REPLIES 14
Super User III
Super User III

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

 





Did I answer your question? Please mark my post as a solution! Kudos are also appreciated!

Proud to be a Super User!




Super User III
Super User III

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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)?

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@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

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@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]),

Hi,

You may download my PBI file from here.

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@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

 

powerbi.png

 

 

 

 

 

 

 

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]`

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors