Select data from single cell with multiple values

johnmcavoy · 12-17-2021 06:39 AM

Hi there,

I have a table I have populated with values from a reference table which looks as follows. The values are derived from the reference table using enumlist and so create a comma seperated value but in the same cell.

table 1

name	values
customer a	apples, pears, kiwi
customer b	orange, plum, strawberry

I would like to create a second table to reference table 1 and manually (or automatically if its easy to do) create individual row entries for each item as follows i.e. select the customer followed by the piece of fruit they had selected in table 1 but have them on separate rows.

table 2

name	values
customer a	apples
customer a	pears
customer a	kiwi
customer b	orange
customer b	plum
customer b	strawberry

Apologies I had posted something similar in another post but wasn’t sure if I was asking the right question or articulating myself correctly. I hope this makes sense?

Thanks

John

Marc_Dillon

johnmcavoy

Thanks Marc, I’ll take a look at that, although it does look complex.

Out of interest, is there an easy manual way that when a user is filling in the form for table 2 that they can select the customer number and then the fruit manually?

This way forward might do for me for now if I can get that working?

I will also try and get the automated way working too.

Cheers

John

Marc_Dillon

Not quite sure what you’re asking about. Maybe you’re asking about creating dropdowns from other data, and/or dependent dropdowns?

johnmcavoy

yes that’s pretty much what I want to do. I want to populate table 2 using the references as follows.

Customer A or Customer B

Then I want to select only the fruit that each customer has previously selected so that I can do further data work.

Customer A can only select from apples, pears and kiwi

Customer B can only select from orange, plum, strawberry

The problem I have though is that whilst I can reference and select the customer in table 1 using the following ‘table 1[name]’

When I use the same reference approach for the values column I only get a single selection which includes all the three entries ’ table 1[values]’ comes back as apples, pears, kiwi

I want the selection to be individual rather than grouped still together i.e.

apples
pears
kiwi

rather than

apples, pears, kiwi

Make sense?

Cheers

John

Marc_Dillon

Oh. Yah I completely misunderstood what you were asking for.

Is [values] column in Table 1 an EnumList?

Assuming it is, use an expression like this in valid_if for [values] on Table 2

SPLIT( SELECT(
  Table1[values] ,
  [name] = [_THISROW].[name]
) , "," )

gekka

Hy, i have similar conditions. Basically i have report table that have colomn Date, Job Name, Worker.

In My App, one Job can have multiple Worker. So the column Worker sometimes hold two names (e.g John, Smith).

I have dashboard to filter that data when i need it. The problem is my formula only pick the row that only hold one value (one job one worker). If the row have two name (John, Smith) the formula not pick any name so the result filter not accurate.

What formula to write so i can get data from the row that hold two names. So i can pick either one of them or both.

Steve

Please start a new topic for help with this.

johnmcavoy

You’re a legend! That’s done the trick. Thank you so much

Cheers

John