Unanswered

Need to move columns to rows in powerapps collections

(0) Share

Report

Posted on by rahulswimmer

I will go through the whole issue from its most granular level.

Following is my collection structure in PowerApps (displaying in table for easy to understand purpose): say colTable1 - >

Here the dates (25/01, 26/01,...) will keep on changing as and when new week is selected

25/01 26/01 27/01 28/01 29/01 30/01 31/01

Project	Task	Description	Mon	Tue	Wed	Thu	Fri	Sat	Sun
Ford	Development	develop tyre	3	4	0	0	0	0	0
Ford	Design	design steering wheel	2	0	0	0	0	0	0
Hyundai	Testing	Test speed	0	1	0	0	4	2	0

Now, I want to convert the above collection (table) to this: say colNewTable

Project	Task	Description	Date	Hours
Ford	Development	develop tyre	25-Jan	3
Ford	Design	design steering wheel	25-Jan	2
Ford	Development	develop tyre	26-Jan	4
Hyundai	Testing	Test speed	26-Jan	1
Hyundai	Testing	Test speed	29-Jan	4
Hyundai	Testing	Test speed	30-Jan	2

So basically the Date column (dynamic column, as it keeps on chaning based on week) is moved to multiple rows.

What I tried:

// For monday
Collect(
 colPatchBack,
 ForAll(
 gallery.AllItems,
 {
 Project: //Project from dropdown,
 Task: // Task from dropdown,
 Description: // Description from input box,
 Date: // Mon date,
 Hours: Value(input_mon.Text)
 })
 )
 )
// For tuesday
Collect(
 colPatchBack,
 ForAll(
 gallery.AllItems,
 {
 Project: //Project from dropdown,
 Task: // Task from dropdown,
 Description: // Description from input box,
 Date: // Tue date,
 Hours: Value(input_tue.Text)
 })
 )
 )

// For other week days
.....

What happens here is, it takes the Monday date and repeats value from first row and duplicates it for other rows.

Can I get some help here ?

Categories:

Building Power Apps

I have the same question (0)

All responses (9)

Answers (0)

v-bofeng-msft on at

Like (0)

Report
Hi @rahulswimmer :

Please try this formula:

// For monday Collect( colPatchBack, ForAll( Filter(gallery.AllItems,Value(input_mon.Text)>0), { Project: //Project from dropdown, Task: // Task from dropdown, Description: // Description from input box, Date: // Mon date, Hours: Value(input_mon.Text) }) ) ) // For tuesday Collect( colPatchBack, ForAll( Filter(gallery.AllItems,Value(input_tue.Text)>0), { Project: //Project from dropdown, Task: // Task from dropdown, Description: // Description from input box, Date: // Tue date, Hours: Value(input_tue.Text) }) ) ) // For other week days .....

Best Regards,

Bof

Was this reply helpful? Yes No
rahulswimmer 87 on at

Like (0)

Report

@v-bofeng-msft Your solution only excludes the rows where hours are 0 for a column.

I am still getting repeated value for all rows.

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like (0)

Report

@rahulswimmer

Your table is an example of flawed design. Rather than complicated work arounds, it should be normalized. It contains several one to many relationships. Please see my blog posts for information as to how to design your app. https://powerusers.microsoft.com/t5/News-Announcements/Database-Design-Fundamentals-and-PowerApps-An-Overview/ba-p/184485

Was this reply helpful? Yes No
rahulswimmer 87 on at

Like (0)

Report

@Drrickryp I will review the database model and schema.

Also I got this table structure from an existing template.

In the mean time, can I achieve what I need in powerapps ?

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like (0)

Report

@rahulswimmer

I think that once you get the tables properly designed, the solution will be obvious.

Was this reply helpful? Yes No
rahulswimmer 87 on at

Like (0)

Report

@Drrickryp There is only 1 table in my question above. With which table did you join and arrive at the conclusion that design is flawed? The second table is how I want the collection to look like in powerapps.

Also, I did a round of review with my colleagues for database normalization. Its normalized to its most granular form.
Nothing further can be done here.

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like (0)

Report

@rahulswimmer

IMHO, your table should be deconstructed to 3 tables with one to many relationships between them. I would design it as Project : Category : TaskDetail . Each would have a unique ID column.

Project table would be at minimum, an ID and Project name, additional columns could be added that relate specifically to the project.

The Category table would contain a minimun of a unique ID, projectID, and CategoryName.

The TaskDetail table would contain an ID, categoryID , Description, TaskDate and Hrs fields.

An example layout would be a Dropdown control with Projects, A second Dropdown with Categories filtered by the Project selected in the first dropdown (ie. Items property: Filter(Category, projectID = Dropdown1.Selected.ID) and a gallery with Items, TaskDetails filtered by the second dropdown.

I would have a form for entering the Description, Date and hrs and a the categoryID from the second dropdown.

However, that's just my opinion.

Was this reply helpful? Yes No
rahulswimmer 87 on at

Like (0)

Report

@Drrickryp Thanks for the explanation.

I have managed to do the same.

But my requirement is to transpose table back and forth, rather the transpose functionality of PowerBI.

Was this reply helpful? Yes No
v-bofeng-msft on at

Like (0)

Report

Hi @rahulswimmer :

Sorry, I am not sure if I understand the meaning of "getting repeated value for all rows".If the function you need is ‘transpose the table’, then I’m afraid PowerApp does not have any function that can directly implement this feature.I suggest you keep the original formula.

Best Regards,

Bof

Was this reply helpful? Yes No