Query Editor - Expand rows

[jmaikido]

Hi folks,

image.png1238×163 11.6 KB

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

[TomMartens · Accepted 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