cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
skalkhathran
Regular Visitor

stacked column in excel file

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 fruitspricelocationfruit kind
2010Londongreen apple
3315Parisbanana
5120Portugalred 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.

1 ACCEPTED SOLUTION

Accepted Solutions

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

 

Best Regards,
Qi

View solution in original post

3 REPLIES 3
v-qiaqi-msft
Community Support
Community Support

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

 73.png

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

010602Gif.gif

Best Regards,

Qi

Best Regards,
Qi
skalkhathran
Regular Visitor

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

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

 

Best Regards,
Qi

Helpful resources

Announcements
PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Top Solution Authors
Top Kudoed Authors
Users online (1,225)