PQ Trim table down to one row per identity

Hello all

I have a table with identities, that have different accounts with different attributes on different applications. Some identities also have more than one account on the same application.

I have made a demo setup and it looks like this:
OneRowPerIdentity1.png

So for instance, it shows that Identity1 has 2 accounts on the AppX application, which has 3 attributes and that those attributes have different values on the 2 accounts. Please note, that the attributes are also named differently on the applications in my actual setup, but for clarity, I have called them Att1, Att2 and Att3 on all applications in this demo.

It is generic how many accounts an identity might have on an application - some applications might have identities that have 3 accounts, while other applications might only have 1 or 2 account per identity.

What I would like to do, is to trim this down to one row per identity, so all the applications and attributes for each identity are shown horizontally, like this:
OneRowPerIdentity2.png

To make this generic, it will have to make columns for each attribute times the max number of accounts any identity has on that given application, for instance indicated by a prefixed number 1. and 2. etc. (eg 2.AppX.AccountID and 2.AppX.Att1, for the second account). I hope this makes sense

I have tried to mess around with this in powerquery for a while now with grouping and pivoting etc., but I simply cant get it to work, and I would really appreciate any help with this.

Thanks in advance and best regards
Imbizile

Thanks so much for your help Bo_Ry - your powerquery skills are truly incredible!!

But I just tried to add a new application to Identity3 called NewSystem with 4 attributes:
OneRowPerIdentity3.png

Everything works great, except for the numbering of accounts. I would expect to get the columnnames respectively prefixed with 1. and 2. (since the max number of accounts any identity has on this application is 2), but instead it gets prefixed with a. and d.:
OneRowPerIdentity4.png

I might have been unclear about the names in the demo, but I only made them like AppY and 1Y2 etc. in order to make the setup understandable. In my actual setup I have 50.000+ identities on 15 different applications and some applications have 50+ different attribute and there is no strict namingconvention across those

Would really appreciate help with the prefixing issue

Best regards
Imbizile

Thanks a bunch for your help Bo_Ry!!

I have tried to apply your code to my actual data, which contains a table with more than 2.5 million rows and it works perfectly. Though some attributes are multivalued (ie different values on more rows for the same attribute) and I do have to filter those out first in order for it to work for now. I will need to combine those multivalued attributes rows into one row where it is listed with a delimiter. But you have previously helped me with something similiar in this thread https://www.excelforum.com/excel-gen...ts-column.html and I will see if I can apply that solution into this somehow.

So again, thank you very much for your effort