Forum Discussion
Pivoting distinct values from column into row
Hi 🙂
I have been battling this issue for a day now and could really use some support!
I am not new to power query but I am no expert.
My issue is this:
I am pulling in data from an API and have whittled through hundreds of columns to define the information I actually need. I now have 40 columns (yay). The data is from Jira and so I am looking at unique issue tickets with relative information. I have 4 columns where there are multiple values per ticket and so instead of having 1 line per ticket, it ranges from 2 to 200 lines per ticket due to one of many combinations of multi-value selection made.
Example:
What I would like is something like this:
or a concat of the values in the same columns e.g. W.Lows, T.Robert.
I hope this makes sense - Thank you!
2 Replies
Reference your query three times.
a) on the first reference, remove columns Teams and Program.
b) on the second one, remove Program and unpivot Teams.
c) on the third one, remove Teams and unpivot Program.
d) Merge the first and the second one, expand Teams.1, Teams.2.
e) Merge the first and the third one, expand Program.1, Program.2.Hi @ArmandoFranco - thanks for such a quick response!
I had considered something like this, my concern with that method is that the query is big and takes a while to update due to the amount of data from the API link, and so duplicating it 3 times would significantly slow down PBI /: - but should there be no other workarounds I might have to do something like this!
