Essentially I have a source Excel file that is a consolidated piece of work sent to me from an internal security group. It contains AD accounts. I have used the PBI AD data source connector, but for this work I use the excel source as it contains accounts from multiple ‘secure’ domains.
There are some 35 columns in total. One column contains the User account. This is of course pivotal. Another column, contains a series of AD Groups that the account is a member of. This Column is called ‘memberOf’ (Of course it is)!
So for each row containing one User Account there are ‘x’ number of AD Groups that the account could be a member of. For example there could be seven individual AD Groups (ie 7 rows within the one cell) for the one row containing the User Account.
For the example above I need to be able to create 7 rows. ie 7 multiples of the User Account for EACH AD Group. Thus I have called the topic ‘denormalize’.
I hope I this makes sense to my learned PBI colleagues.
@jmaikido
I’m sure this can be done. I tried creating a quick dummy version in excel , but when I imported it it put all the “MemberOf” on a single line vs the multiple rows you are showing. Any chance you can upload some dummy data?
This is an interesting scenario. As Nick mentioned though some dummy data here would be essential I think to really get a helpful solution on this one - mainly due to its complexity I think
Hi Nick,
Here’s some sample data. Only 2 records. If you 2xclick each MemberOf cell, it will display the list of all the AD Groups. But if you only just click on each MemberOf cell you only see one Group in the above task bar.
I’m thinking the column may need to be split using perhaps a carriage return delimiter?
Regards,
John
