stacked column in excel file

[skalkhathran]

how to separate data in stacked column in excel? and have them as separate categories for other values in other columns.

here is an example of my excel file:

number of fruits	price	location	fruit kind
20	10	London	green apple
33	15	Paris	banana
51	20	Portugal	red apple

in power app, i want to have drop down box for the location that change the data in the following either dropdowns or text input "have no idea":

                                      Red apple            banana          green apple

price                                     20                      15                10

number of fruits                   51                      33                 20

and the above values will be changed based on the location as it will be a long list of repetitive cities. is that possible? i tried to filter the dropdown box to have one value as banana or apple but doesn't work.

[v-qiaqi-msft]

Hi@skalkhathran，

Based on the issue that you mentioned, do you want to populate a Dropdown or TextInput for other columns based on the location?

Could you please share a bit more about your scenario, is there any primary key in your Excel table?

If there is a primary key within your data source, the value will me more easier to determined.

I have a test on my side, please take a try as below.

Add 4 Dropdowns and set the Items property as below:

Distinct(Table1,location)// For location Dropdown

Filter(Table1,location=Dropdown1.Selected.Result).'fruit kind'// for fruit kind Dropdown

Filter(
    Table1,
    location = Dropdown1.Selected.Result && 'fruit kind' = Dropdown2.Selected.'fruit kind'
).price // for price Dropdown

Filter(
    Table1,
    location = Dropdown1.Selected.Result && 'fruit kind' = Dropdown2.Selected.'fruit kind' && price = Dropdown3.Selected.price
).'number of fruits' // for 'number of fruits' Dropdown

 

Or you can try the GroupBy() function to display it in a Gallery.

Set the Items property of the Dropdown set for the location as below:

Distinct(Table1,location)

Set the Items property of the Gallery as below:

GroupBy(Filter(Table1,location=Dropdown5.Selected.Result),"fruit_x0020_kind","Fruit")

Set the Text property of the Labels within the Gallery as below:

ThisItem.fruit_x0020_kind

"price is:"&Concat(ThisItem.Fruit.price,price,",")

"number of fruits is:"&Concat(ThisItem.Fruit.'number of fruits','number of fruits',",")

Please check the following GIF

Best Regards,

Qi

[skalkhathran]

Thank you Qi for the great support. will go with the option for the gallery. however, how can i set up the drop down box of the location to show only the city of Paris and exclude the other cities from the drop down box and the gallery. would that be possible? to filter the values in the drop down

[v-qiaqi-msft · Accepted solution]

Hi@skalkhathran，

Do you mean that you want to make the Dropdown filtered only with the "Paris" option?

You could set the Items property of the location Dropdown as below:

Filter(Table1,location="Paris")​.location​

Filter(
    Table1,
    location = Dropdown4.Selected.location
).'fruit kind'

Filter(
    Table1,
    location = Dropdown4.Selected.location && 'fruit kind' = Dropdown5.Selected.'fruit kind'
).price

Filter(
    Table1,
    location = Dropdown4.Selected.location && 'fruit kind' = Dropdown5.Selected.'fruit kind' && price = Dropdown6.Selected.price
).'number of fruits'

Go for the Gallery, you could repeat as below.

Set the Items property of the location Dropdown as below:

Filter(Table1,location="Paris")​.location

Set the Items property of the Gallery as below:

GroupBy(Filter(Table1,location=Dropdown4.Selected.location),"fruit_x0020_kind","Fruit")

Set the Text property of the Labels within the Gallery as below:

ThisItem.fruit_x0020_kind

"price is:"&Concat(ThisItem.Fruit.price,price,",")

"number of fruits is:"&Concat(ThisItem.Fruit.'number of fruits','number of fruits',",")

Regards,

Qi