Burgers & Bytes
May 22, 2024

Power Apps: Transpose Excel Data using Power Fx

May 22, 2024  •  3   • 591 
Table of contents

A lot of business processes are supported by data stored in Excel sheets. Working with Power Apps often eliminates the use of an Excel sheet, but in some cases the data needs to be stored into an Excel sheet.

In this blog the concept of transposing the data from an Excel table will be described, so you can use this data throughout the app.


As an input there is an Excel table in which there are multiple rows and multiple columns. The first column has the Country value in it and the other columns describes the products of that Country.

Excel Table

The end result is a gallery in Power Apps that can be filtered by country.

Filter gallery transposed table

Add Excel as data source

Store an Excel file into a SharePoint or OneDrive location. In this Excel the data must be converted into a table. It’s helpful to give the table a proper name. In this scenario: CountryProductTable

Select Excel as data source and navigate to the location of your Excel sheet and select the table you want to use. Select data source

Load data into Collection

The data of the Excel is loaded into a collection named: colExcelData. ClearCollect(colExcelData, CountryProductTable);

The original Excel data is now similar to the collection, both content wise as structure based on columns. colExcelData

Screen elements

On the screen a combobox is used to select a filter. In the Items property of the combobox the values of the Country column are shown: SortByColumns(colExcelData, "Country", SortOrder.Ascending)

Items combobox

Load data from specific selection

The collection contains all data, but it needs to be filtered to the selected country.

ClearCollect(colSelectedCountry, Filter(colExcelData, Country = cmbCountry.Selected.Country));

The filtered collection contains the following data:


Transpose data using JSON

This table needs to be transposed so it can be displayed in the correct way in the gallery. First step is to format the collection into JSON:

JSON(colSelectedCountry, JSONFormat.IgnoreBinaryData)

The output of this part of the formula looks like:


The unnecessary characters needs to be substitute and for all separate results a new collections needs to be build which contains Product and Value.


This needs to be added on the OnChange of the combobox.

//Create collection based on selected Country
ClearCollect(colSelectedCountry, Filter(colExcelData, Country = cmbCountry.Selected.Country)); 

    // create table of records in JSON format and substitute out the unneeded characters
    ), {Result: ThisRecord.Value}),
    // Transpose data
            Product: Substitute(Left(
                ) - 1
            ), """", ""), 
            Value: Substitute(Mid(
                ) + 1
            ), """", "")

//The value for country is also set as a Product, this needs to be removed from the collection
Remove(colTransposedCountryProduct, LookUp(colTransposedCountryProduct, Product = "Country"));

The output of this formula results in a collection in which also the Country value is set as a product.

Transposed table

Last step is to filter out the country as a valid value:

Remove(colTransposedCountryProduct, LookUp(colTransposedCountryProduct, Product = "Country"));

End result

The formula is ready and the filtering can be made! Filter gallery transposed table

A disclaimer is needed as well…

Using Excel as a datasource for Power Apps is easy and accessible, but it has some downsides. Excel isn’t built for handling large amounts of data or complex relationships, which can slow down your app. It also has limitations with data security and handling multiple users at once. For more reliable and secure applications, consider using Dataverse or other advanced data solutions. Always assess your project’s needs and choose the best data source.

comments powered by Disqus
Empowering productivity - one blog at a time!