How to ...

[RonaldvdH]

Hey Guys,

I have a question on how to display this situation.

I have a registration based on a Building and a Status

Now I've summarised this table but i want to summarize the status as wel but I do not want multiple colums with numbers in them.

Basically i just want one column but im not sure on how to do that and not sure on the format

I've been thinking about just displaying the distinct options, is this possible ?

Building	Status
Building A	1-2
Building B	3-4-5-6

Building	Status
Building A	1
Building A	1
Building A	2
Building A	2
Building B	3
Building B	4
Building B	5
Building B	6

[ray_aramburo · Accepted solution]

This one is a fun one, so here are the list of steps (need to be done in Power Query):

1) Split your status column by position (1 character) -> this will create a separate colum for the letter prefix and the number

2) Remove Duplicates -> this ensures only unique values on our rows

3) Group All Rows by the Letter column (Status.1)

4) Add a Custom Column to add a List of records (the numerical ones)

5) Click on Extract Values (expanding the Custom column) and select "-" as the custom delimiter

6) Expand All Data column and extract Building column, then remove again duplicate rows

7) Use the merge columns feature to join Letter column with list of values

And there you go!

[RonaldvdH]

@ray_aramburo I had to change my datamodel a little bit but with your play by play I got it to work

[ray_aramburo]

Glad it worked! 🙂