Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
jmaikido
Microsoft Employee
Microsoft Employee

Query Editor - Expand rows

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

1 ACCEPTED SOLUTION
TomMartens
Super User
Super User

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"

image.png

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":

image.png

 

Before the operation the table looks like this:

image.png

 

After the operation the table will look like this:

image.png

 

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

1 REPLY 1
TomMartens
Super User
Super User

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"

image.png

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":

image.png

 

Before the operation the table looks like this:

image.png

 

After the operation the table will look like this:

image.png

 

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.