cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

Convert columns into rows in Power apps Data table

Hi,

we have the data in data table, need to convert the rows in to columns.

 

Columns Name : Quarter

Data                 : YearQ1,Year-Q2,Year-Q3

Column name: Overall avg

Data:33

Column name: Traffic

Data: Red

 

out put shoudl be as below

Columns Name : YearQ1

Data                 :33

Column name: Year-Q2

Data:33

Column name: Year-Q3

Data: 33

 

Please help me to achive the above requirement

9 REPLIES 9
Highlighted
Community Champion
Community Champion

Re: Convert columns into rows in Power apps Data table

You can do this with a GroupBy followed by an AddColumns.

The exact syntax will depend on what you are more specifically trying to do.

Something like:

AddColumns(
    GroupBy(
        YourTable,
        [your grouping criteria],
        "AllRows"
        ),
    "Year-Q1", [not sure if - is a good idea in column names]
    LookUp(AllRows, [criteria], OverallAvg),
etc. etc.
Highlighted
Anonymous
Not applicable

Re: Convert columns into rows in Power apps Data table

No , the Addcolumns is not working out for the below table which is in the attached image.

Because the result of the table itself is acheieved by group by and add columns.so its reconginzing the column names one more time in add columns.

 

 

Highlighted
Community Champion
Community Champion

Re: Convert columns into rows in Power apps Data table

What expression did you try and what was the result?

Highlighted
Anonymous
Not applicable

Re: Convert columns into rows in Power apps Data table

Here is the formula thats creating the attached table. Please suggest how can i change the formula to convert the rows to column and columns to rows.

 

AddColumns(GroupBy(Filter(YearTasks,If(IsBlank(SelectedEndDate)||IsBlank(SelectedStartDate),true,ActualEndDate >= SelectedStartDate && ActualEndDate <= SelectedEndDate)),"YearQ","QuaterwiseTasks"),"Name",YearQ,"EffortAdherence",Sum(QuaterwiseTasks,EffortAdherence)/Count(Filter(QuaterwiseTasks,EffortAdherence=1 ||EffortAdherence=3 ||EffortAdherence=5).ID),"ScheduleAdherence",Sum(QuaterwiseTasks,ScheduleAdherence)/Count(Filter(QuaterwiseTasks,ScheduleAdherence=1 ||ScheduleAdherence=3 ||ScheduleAdherence=5).ID),"QARating",Sum(QuaterwiseTasks,QARating)/Count(Filter(QuaterwiseTasks,QARating>=1 && QARating<=5).ID))

Highlighted
Community Champion
Community Champion

Re: Convert columns into rows in Power apps Data table

Wow, there is a lot going on.

I think it might be easier if I show you what I mean.

Assume that you have loaded your table to a collection like this:

ClearCollect(
    BaseTable,
    Table(
        {Quarter: "2018-Q3", OverallAvg: 2.78, TrafficLight: "Yellow"},
        {Quarter: "2018-Q4", OverallAvg: 2.92, TrafficLight: "Yellow"}
    )
)

Then this is the gallery Items property you would use:

AddColumns(
    Table({DummyColumn: 1}),
    "Q3-2018", LookUp(BaseTable, Quarter="2018-Q3", OverallAvg),
    "Q4-2018", LookUp(BaseTable, Quarter="2018-Q4", OverallAvg)
)

To get this:

 

1.JPG

 

You will need the GroupBy in case the data has more structure to it, but since you have only given me two lines, it is impossible to know.

Highlighted
Anonymous
Not applicable

Re: Convert columns into rows in Power apps Data table

This formula is generating only one row for OverallAvg

 

if i want to create one more row  for Traffic Light , how can i change this formula.

 

AddColumns(
    Table({DummyColumn: 1}),
    "Q3-2018", LookUp(BaseTable, Quarter="2018-Q3", OverallAvg),
    "Q4-2018", LookUp(BaseTable, Quarter="2018-Q4", OverallAvg)
)

Highlighted
Community Champion
Community Champion

Re: Convert columns into rows in Power apps Data table

Something like this:

 

AddColumns(
    Table({RowId: "OA"},{RowId: "TL"}),
    "Q3-2018", LookUp(BaseTable, Quarter="2018-Q3", If(RowId="OA", Text(OverallAvg), TrafficLight)),
    "Q4-2018", LookUp(BaseTable, Quarter="2018-Q4", If(RowId="OA", Text(OverallAvg), TrafficLight))
)
Highlighted
Anonymous
Not applicable

Re: Convert columns into rows in Power apps Data table

Can I add the Quarters-"Q3-2018" dynamically based on the data in collection

 

 

AddColumns(
    Table({RowId: "OA"},{RowId: "TL"}),
    "Q3-2018", LookUp(BaseTable, Quarter="2018-Q3", If(RowId="OA", Text(OverallAvg), TrafficLight)),
    "Q4-2018", LookUp(BaseTable, Quarter="2018-Q4", If(RowId="OA", Text(OverallAvg), TrafficLight))
)

Highlighted
Frequent Visitor

Re: Convert columns into rows in Power apps Data table

Hi,

 

I want to do something very similar but what I have is a TextInput with multiple lines and then save each line a specific column of a collection.

 

How can I use the same logic that you use on this example for this?

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Watch Now

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (4,796)