Pseudo denormalize

Hopefully the graphic I have added can be viewed.

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.

TIA John

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

Nick

Enterprise%20DNA%20Expert%20-%20Small

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

Thanks
Sam

Hi Nick,
Thnx for looking at issue. I’ll aim to upload a sample.
Rgds, John

Hi

Thnx for looking at issue. I’ll aim to upload a sample.
Rgds, John

Modified_Extract.xlsx (12.0 KB)

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

Yep, you were on the right track, want to use linefeed and not carriage return. Also, be sure to split to rows and not columns:


Nick

Enterprise%20DNA%20Expert%20-%20Small

Sweet, thnx Nick for spending the time to look into the issue.
Regards, John