Separating text and lists that are together in one column into two columns

[Anonymous]

Good morning everyone,

I am currently working in Power Query where I have retrieved a table from Liongard. This table is supposed to show the groups list including members of Office 365. I had to expand the row twice to get the list in its current form (see image).

As you can see, there are both text and new lists mixed together. The text you see is the name of the group. The list below contains all the users. What I would like to do is separate this, so I can expand the list of names as well. Right now, the data is not useful to me in this format.

What I would like to achieve is to have two columns: one with the name of the group and the row next to it with the user.

Example:
Group              NameMember 
X                      *name member* 
X                      *name member* 
X                      *name member* 
Y                      *name member* 
Y                      *name member* 

I hope someone can help me with this!

Thanks in advance for the assistance!

Kind regard,

Lisanne

[AlienSx]

let
    Source = #table(
        {"Group"}, 
        {{"All Company"}, {{"Joseph", "Stalin"}}, 
        {"Alle gebruikers"}, {{"Lisanne", "Asked"}}}
    ),
    group = Table.Group(
        Source, "Group", 
        {"x", (x) => x[Group]{1}}, 
        GroupKind.Local, 
        (s, c) => Number.From(Value.Is(c, type text))
    ),
    xp = Table.ExpandListColumn(group, "x")
in
    xp

[Anonymous]

Hi @AlienSx ,

Thanks for responding. I have more groups than the ones mentioned above; those were just examples.

Do you know the best way to handle this?

Kind regards,

Lisanne

[AlienSx]

@Anonymous replace Source table with a reference to your table and see what happens. 

[omjcontabil]

I'm starting out in power query and I wanted to understand what it means: {"x", (x) => x[Group]{1}}, and also: (s, c) => Number.From(Value.Is(c , type text)), thank you in advance for your explanation.

[AlienSx]

@omjcontabil read Table.Group documentation. {"x", (x) => ...} is 

aggregatedColumns as list

Grouping can be global or local. We are dealing with GroupKind.Local in this case. (s, c) => function must return 1 or 0 - that's Table.Group's requirement. When function returns 1 then new group is started. 

[Anonymous]

Hi @AlienSx ,

Thanks again for responding. When I load the table, I only get lists. I expand these, and then I end up with the situation described above. If I add your code in the advanced editor, I lose that as well.

I don't understand the best way to do this. How should I proceed?

Kind regards

Lisanne

[AlienSx]

@Anonymous my code works with table (of single column like in your sample) with the following structure: text - list - text - list - text -.... Make sure that input data (the table you are loading) has the same structure and then apply my code.

[Anonymous]

Hello @AlienSx ,

Thank you for your response!

I don't understand this part of the query:
{{"All Company"}, {{"Joseph", "Stalin"}},

{"All Users"}, {{"Lisanne", "Asked"}}}

Do I have to do this for all the groups? So, do I need to specify each group and its members? I have more than 100 groups with users so this wouldn't be feasible for me.. 

I look forward to hearing from you!

Kind regards,
Lisanne

[AlienSx · Accepted solution]

@Anonymous I just imitated your table with #table() statement because you did not provide data in usable format. Suppose your table has single column named Group. Then:

let
    Source = your_table,
    group = Table.Group(
        Source, "Group", 
        {"x", (x) => x[Group]{1}}, 
        GroupKind.Local, 
        (s, c) => Number.From(Value.Is(c, type text))
    ),
    xp = Table.ExpandListColumn(group, "x")
in
    xp

[Anonymous]

Hi @AlienSx ,

Sorry for not being clear. There are multiple columns in that table. How can I best approach this?

I look forward to hearing from you!

Best regards,
Lisanne

[AlienSx]

remove other columns