Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code FABINSIDER for a $400 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi folks,
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 5 individual AD Groups (ie 5 rows within the one cell) for the one row containing the User Account.
For the example above I need to be able to create 5 rows. ie 5 multiples of the User Account for EACH AD Group. I require ONE row for the same user, but duplicating ALL other column values for EACH AD Group the user is a MemberOf.
I hope I this makes sense to my learned PBI colleagues.
TIA John
Solved! Go to Solution.
Hey,
this can be easily done, unfortunately it's not that obvious. I assume the rows are separated by a line feed.
In the query editor mark the column that contains the groups and open the context menu and choose "Split Column" --> "By Delimiter"
Choose the settings accordingly to the dialog below, please notice that input field will be filled automatically after you have selected the special character "Line Feed":
Before the operation the table looks like this:
After the operation the table will look like this:
Be aware that the operation will replace the original column, if you want to keep it you have to duplicate the column.
Hopefully this is what you are looking for.
Regards,
Tom
Hey,
this can be easily done, unfortunately it's not that obvious. I assume the rows are separated by a line feed.
In the query editor mark the column that contains the groups and open the context menu and choose "Split Column" --> "By Delimiter"
Choose the settings accordingly to the dialog below, please notice that input field will be filled automatically after you have selected the special character "Line Feed":
Before the operation the table looks like this:
After the operation the table will look like this:
Be aware that the operation will replace the original column, if you want to keep it you have to duplicate the column.
Hopefully this is what you are looking for.
Regards,
Tom
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the February 2025 Power BI update to learn about new features.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
84 | |
69 | |
68 | |
39 | |
37 |