Convert row into column

(0) Share

Report

Posted on by Jackson_Dhorty

108

Hi,

I have a collection record something like this:

myCollection

Name	Tags
tagName1	Tag1
tagName1	Tag2
tagName2	Tag2
tagName2	Tag3
tagName3	Tag1
tagName4	Tag4
tagName5	Tag2
tagName5	Tag4

I need to dynamically convert duplicate names in the name column into a separate column with their respective tags like this:

tagName1	tagName2	tagName3	tagName4	tagName5
Tag1	Tag2	Tag1	Tag4	Tag4
Tag2	Tag3			Tag2

In the collection, in the Name column value can be more than 5.

can someone please suggest me a possible solution on that? would be appreciated

Thanks in advance

Categories:

Building Power Apps

All responses (6)

Answers (1)

Sort by

RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@Jackson_Dhorty
You cannot dynamically create columns in PowerApps. It enforces a strict declarative signature for records.

The best you would be able to do on this is to group the data.

Example: GroupBy(myCollection, "Name", "_recs")

This would provide you with a two column table as a result. The first column will be Name and it will be the distinct list of Names in the data table. The next column is _recs and it is a table column that contains all the records that match that name.

So, you would have:

Name _recs

tagName1 [Tag1, Tag2]

tagName2 [Tag2, Tag3]

..etc..

I hope this is helpful for you.
Jackson_Dhorty 108 on at

Like
a
(0)

Report
Copy link

Link copied!

I have tried this approach but issue is when i am trying to count a specific tag in this column _recs, its not working

Thanks
mahoneypat 1,720 on at

Like
a
(0)

Report
Copy link

Link copied!

You can use this syntax to get your count.

AddColumns(GroupBy(myCollection, "Name", "_recs"), "TagCount", CountA(_recs))

Regards,
Pat
Jackson_Dhorty 108 on at

Like
a
(0)

Report
Copy link

Link copied!

it will give all counted tags but i want is:
tagName1 has count of Tag2 is 3
Name _recs
tagName1 [Tag1, Tag2, Tag2, Tag2, Tag4]
tagName2 [Tag2, Tag3]

thanks
Verified answer

mahoneypat 1,720 on at

Like
a
(1)

Report
Copy link

Link copied!

Ah. I get it. Here are two similar ways to do that. The first adds a column with a value of 1 for each row and then groups by both the Name (tagName) and Tag, sums the added count column. Also below is the full code I used in a button to create this collection to test this out.

ClearCollect(colGrouped, AddColumns(GroupBy(AddColumns(colTags, "subcount", 1), "Name", "Tags", "Rows"), "Count", Sum(Rows,subcount)))

This also works (w/o adding the subcount column), I figured out 2nd.

ClearCollect(colGrouped2, AddColumns(GroupBy(colTags2, "Name", "Tags", "Rows"), "Count", CountRows(Rows)))

ClearCollect(colTags, Table({Name:"tagName1",Tags:"Tag1"},{Name:"tagName1",Tags:"Tag1"}, {Name:"tagName1",Tags:"Tag2"},{Name:"tagName2",Tags:"Tag2"},{Name:"tagName2",Tags:"Tag3"},{Name:"tagName3",Tags:"Tag1"},{Name:"tagName4",Tags:"Tag4"},{Name:"tagName5",Tags:"Tag2"},{Name:"tagName5",Tags:"Tag4"}));
ClearCollect(colGrouped, AddColumns(GroupBy(AddColumns(colTags, "subcount", 1), "Name", "Tags", "Rows"), "Count", Sum(Rows,subcount)))

Pat
Jackson_Dhorty 108 on at

Like
a
(0)

Report
Copy link

Link copied!

It worked but I don't want to hard code tags name like tag1, tag2 etc.

As you can see _recs column has tags in grouped. For example I only want count of tags2 which against tagName1 column.

Thanks