Consolidate Values from unknown # of columns (JIRA Sprint Reports)

[Anonymous]

Hello - I'm trying to create a Power BI report based on data from JIRA (specifically Star JIRA, but that's likely not relevant).  For anyone not familiar, JIRA is used to track software development requests.  As a request is worked through, its put into a "sprint" which is a 2-3 week development cycle.  Sometimes a request will be pushed back to the next sprint, or the one after, etc., until it's completed.

In the reports extracted from JIRA, the sprint field will show every sprint that a particular request was part of, until it was completed.  So the # of sprint columns can vary each time the report is run (Sprint columns = max number of sprints for any of the requests in the report).  

The first time the report us run, it might look like this:

Issue Key	Sprint	Sprint	Sprint	Sprint
ES_1	Sprint72	Sprint73
ES_2	Sprint68
ES_3	Sprint71
ES_4	Sprint71	Sprint72	Sprint73	Sprint74

But then if ES_5 is added the next time the report is run and it was in more sprints, the report would be:

Issue Key	Sprint	Sprint	Sprint	Sprint	Sprint	Sprint	Sprint	Sprint	Sprint
ES_1	Sprint72	Sprint73
ES_2	Sprint68
ES_3	Sprint71
ES_4	Sprint71	Sprint72	Sprint73	Sprint74
ES_5	Sprint73	Sprint74	Sprint75	Sprint76	Sprint77	Sprint78	Sprint79	Sprint80	Sprint81

I need the report to capture the Max (last) sprint for each issue, but I don't know how to handle the fact the # of columns could change each time. 

Please help!

[AlexisOlson · Accepted solution]

In the query editor, unpivot all except the Issue Key column.

The result will be a table with a constant number of columns:

[Anonymous]

Thanks very much, this worked perfectly.  After unpivoting I was able to filter for just the "Sprint" headers/values, buffer + sort by Sprint # and then remove duplicates to only leave the Max value.  Once that's done I can merge the value back into the initial data set.

Cheers!