Answered

Filter the repeating data accross the 2 SharePoint Lists

(0) Share

Report

Posted on by The_api_guy

How can I filter a table to only show me the repeating field1 records
Products ----------------- filter -------------- Resulting table
ItemID ItemCount
1124 3
1124 4
2745 41
1347 3
1347 41
5758 45
6345 31

Note: The data is sample data, I cant provide original data because of data security issues. Sorry about that.

expected result is:
ItemID Itemcount
2745 41
5758 3
6345 31

Please help me

Categories:

Building Power Apps

All responses (14)

Answers (2)

Sort by

WarrenBelz 156,730 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @The_api_guy ,
You will get three columns out of this
AddColumns( GroupBy( YourListName, "ItemID", "OtherData" ), "TotalCount", Sum( OtherData, ItemCount ) )
ItemID (one of each)
TotalCount - the figure you want
OtherData - a record with all the fields belonging to each ItemID - you can ignore this.
Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.
Verified answer

KrishnaV 5,023 on at

Like
a
(2)

Report
Copy link

Link copied!
Hi @The_api_guy ,

Try this:
ClearCollect(colls1,GroupBy(collSampleData,"Field1","Field2")); // dummy collection for grouping ClearCollect(colls2,AddColumns(colls1, "Count", CountRows(Field2)) ,GroupBy(collSampleData,"Field1","Field2")); // dummy collection for count the repetation Clear(collFinal);//Final collection with the desired output ForAll(Filter(colls2,Count=1),Collect(collFinal,{Field1:Field1,Field2:First(Field2).Field2})); Clear(colls1); Clear(colls2);

I hope this resolved your issue if you see any challenge/need further help please let me know I am always happy to do it for my community.

Regards,
Krishna
If this post helps you give a 👍 and if it solved your issue consider Accept it as the solution to help the other members find it more.
WarrenBelz 156,730 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

@KrishnaV ,
Interesting complex solution
@The_api_guy please tell me if I missed something in your requirements
KrishnaV 5,023 on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you @WarrenBelz, I have worked on a similar problem and it worked perfectly for me.
@The_api_guy : Please let me know which one worked for you.

I am 100% if @WarrenBelz solution helps you also 🙂
New2PA2020 47 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @WarrenBelz & @KrishnaV ,

Below is the output for both of your solutions:
So Krishna solution gave me the required output!
Thank you for your help guys.
New2PA2020 47 on at

Like
a
(0)

Report
Copy link

Link copied!

I was also looking for similar kind of solution but I need to work further on my case.
The_api_guy 50 on at

Like
a
(0)

Report
Copy link

Link copied!

WOW, I am really impressed and someone else is also experiencing similar issue.
@New2PA2020 thank you for your time to work on both the solutions. Yes I too agree that @KrishnaV code worked for me. Sorry Warren your code is still showing me the repeated rows as part of the output.
WarrenBelz 156,730 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

@New2PA2020 ,
Good that it fixed your issue, although mine did exactly what I wanted it to - I must not understand your requirements
How can I filter a table to only show me the repeating field1 records which still is not clear to me matched with the required output.
The_api_guy 50 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @WarrenBelz ,

What I want is only the unique records out of the collection. I want to get rid of the repeated items in my collection.
The_api_guy 50 on at

Like
a
(0)

Report
Copy link

Link copied!

@WarrenBelz ,

Please let me know if you have any better solution, I am more than welcome to try with that.