Power Platform Community Forum Thread Details

Hello,

Below is a view of my excel table. I'm looking to replicate similar one into Power Apps form.

Column B,C and D are constant columns. We can edit this text boxes as an when required. PENNSYLVANIA and MASSACHUSETTS are my location columns (which we can apply as headers in PowerApps)

In Column E and F, I have a Yes and No dropdown. Based on this percentage is calculated.

For e.g. for PENNSYLVANIA , there are total 4 counts, if all the selections are marked as Yes, Total % will be 100%

Similarly for MASSACHUSETTS, there are total 4 counts. Out of this 4 counts, 3 are Yes are 1 is No, so the percentage that is calculated is 75%

No. of columns will defer accordingly to Locations. In 1st section it has 4 parameters and in 2nd section it has 7 parameters. I have multiple other locations

Is this doable in PowerApps. Appreciate if someone can direct with step by steps to achieve this. Thanks!

Categories:

Building Power Apps

Yes, this is absolutely achievable in Power Apps. Here's a clear, step-by-step approach to replicate your Excel structure into a Power Apps canvas app form:

✅ Step-by-step Guide to Achieve Your Excel Logic in Power Apps

Step 1: Prepare your data source

You can store your Excel data in:

SharePoint List

Dataverse

Excel Online (OneDrive) (least recommended for complex apps)

For scalability and ease of management, SharePoint List or Dataverse is recommended.

Example structure for SharePoint List:

Column Name	Type	Description
Title	Single Line Text	(e.g., Huey Pierce Long)
Location	Single Line Text	(e.g., PENNSYLVANIA, MASSACHUSETTS)
Section	Single Line Text	(e.g., Section1, Section2)
Response	Choice (Yes/No)	Dropdown for selection

Step 2: Set up your Power Apps Canvas App

Create a new Canvas app and connect your data source.

Add a Gallery or Data Table to display rows and columns clearly.

Step 3: Dynamic layout for your app

To achieve a similar tabular structure:

Add a Gallery:
- Set Items property to your data source.
- Group your data by section/location.
Insert Textboxes for Columns B, C, D:
- Make these editable by users.
- Bind each textbox with the data source's respective columns (Title or additional columns for each constant field).

Example for textbox default property:

ThisItem.Title

Step 4: Dropdown (Yes/No Selection)

Within your gallery, add dropdown controls for column E, F, etc.

Set dropdown items as:

["Yes", "No"]

Bind the default value of the dropdown to your data source response field:

ThisItem.Response.Value

Step 5: Calculate Percentage

To calculate percentages dynamically, use a label control above each column header.

Example Percentage Calculation (for PENNSYLVANIA):

Text( (CountRows(Filter(YourDataSource, Location = "PENNSYLVANIA", Response.Value = "Yes")) / CountRows(Filter(YourDataSource, Location = "PENNSYLVANIA"))) * 100, "[$-en-US]#%" )

Replace "PENNSYLVANIA" with your location names accordingly.

Step 6: Handle Multiple Locations and Parameters

For scalability, use collections to group locations and parameters:

Example collection loading in OnStart:

ClearCollect( Locations, Distinct(YourDataSource, Location) );

Use horizontal galleries or flexible layout containers to dynamically add multiple columns (locations).

Step 7: Submit Edited Data

Add a save icon/button.
On its OnSelect property, update your source:

ForAll( Gallery1.AllItems, Patch( YourDataSource, LookUp(YourDataSource, ID = ThisRecord.ID), { Title: YourTextBox.Text, Response: {Value: YourDropdown.Selected.Value} } ) )

If you encounter any issues or require further customization, feel free to ask!