Unanswered

Distinct value listing for values in multiple columns

(1) Share

Report

Posted on by CU15090428-0

Hi there,

I have problem to create distinct listing from all items of different columns (Subject_1, Subject_2 and Subject_3), Distinct (Ref, Subject_1) works fine for single column.

but all the suggestions for multiple columns from Copilot do not work. Any Clue ??

Stephen

Categories:

Power Apps Pro Dev & ISV

I have the same question (0)

All responses (13)

Answers (1)

Manigandan Shreedharan on at

Like (1)

Report
If you are using Power Query, here's how you can combine distinct values from multiple columns:

In the Power Query editor, create three new columns, one for each subject column.

Use the Unpivot feature:

Select the Ref, Subject_1, Subject_2, and Subject_3 columns.

Right-click and choose Unpivot Columns.

After unpivoting, you will have a single "Value" column containing all subjects.

Then, remove duplicates by selecting the new "Value" column and choosing Remove Duplicates from the ribbon.

Was this reply helpful? Yes No
Manigandan Shreedharan on at

Like (1)

Report

Let me know which environment you are working with, and I can provide more specific guidance!

Was this reply helpful? Yes No
WarrenBelz 153,034 Most Valuable Professional on at

Like (2)

Report
Hi CU15090428-0

Use GroupBy (you need the last field to contain a Table with the other matching fields for each group)
GroupBy( Ref, Subject_1, Subject_2, Subject_3, Grouped )

Please click Does this answer your question 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 a Like.
MVP (Business Applications) Visit my blog Practical Power Apps Buy me a coffee

Was this reply helpful? Yes No
CU15090428-0 18 on at

Like (1)

Report

Hi Warren Thanks

the Groupby seems partially work. But it seems still not showing the distinct listing. And I cannot add in the SortbyColumn bit.

Was this reply helpful? Yes No
WarrenBelz 153,034 Most Valuable Professional on at

Like (1)

Report
Hi CU15090428-0

If you want to sort, it needs to be one of those four columns (example)
SortByColumns( GroupBy( Ref, Subject_1, Subject_2, Subject_3, Grouped ), "Subject_1", SortOrder.Ascending )

I am not sure what you mean by not showing the distinct listing - it will show all distinct combinations of the three relevant columns.

Please click Does this answer your question 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 a Like.
MVP (Business Applications) Visit my blog Practical Power Apps Buy me a coffee

Was this reply helpful? Yes No
Suggested answer

Nandit 1,568 Moderator on at

Like (1)

Report
Hi

Trying using the below formula in your Gallery Items. It takes all the values from three columns, concatenates it then splits it. We are then putting the Distinct function and a Sort on top of that.
Sort( Distinct( Split( Concatenate( Concat( Ref, Subject_1, "," ), ",", Concat( Ref, Subject_2, "," ), ",", Concat( Ref, Subject_3, "," ) ), "," ), Value ), Value, SortOrder.Ascending )

Hope this helps.

Kind regards,

Nandit

If this answers your query, please mark this response as the answer.
If its helpful, please leave a like. Thanks!

Was this reply helpful? Yes No
CU15090428-0 18 on at

Like (1)

Report

Hi Warren,

thank you for the clue which I based on to modify. as below.

SortByColumns(

Distinct(

GroupBy(

Ref,

Subject_1,

Subject_2,

Subject_3,

Grouped

),

Subject_1),

"Value",

If(

SortDescending1,SortOrder.Descending,SortOrder.Ascending)

)

It works to some extent giving the result as below.

But it seems only discrete applied to Subject_1 because when I changed to Subject_2,

the item count became 55 from 80.

Was this reply helpful? Yes No
WarrenBelz 153,034 Most Valuable Professional on at

Like (1)

Report

Hi CU15090428-0

I must admit I am getting a little confused as to what outcome you are seeking here - also the latest code should only produce one column Value, but you are displaying three. The GroupBy() function on multiple columns produces a list of all possible combinations of the grouped columns. Using Distinct on either that or the original list should produce identical results (a distinct summary of each field's possible values).

Was this reply helpful? Yes No
CU15090428-0 18 on at

Like (1)

Report

Hi Warren,

thanks for your reply, The 3 columns is only Warp count 3, (not the 3 orginal Subject columns) .

I agree to what you mentioned. It is expected the distinct after GroupBy should always giving identical counting, disregard to Distinct on which orginal Subject column I changed). But apparently, it did not behave as you said.

By the way, I just figure out what I should do for merging records from different Subject columns and then display in separate gallery with distinct sorting. I need to setup Collection first by an action Button.

Collect(CombineSubjects,

Distinct(Ref,ThisRecord.Subject_1),

Distinct(Ref,ThisRecord.Subject_2),

Distinct(Ref,ThisRecord.Subject_3));

Navigate(AllSubjects, ScreenTransition.Fade)

In the AllSubjects screen, A new Gallery Property > Items> I code as below

SortByColumns(

Distinct(CombineSubjects,Value),

"Value",

If(SortDescending1,SortOrder.Descending,SortOrder.Ascending)

)

It gives the expected display of distinct value listing after merging all records from 3 subject columns.

Thankyou indeed

Stephen

Was this reply helpful? Yes No
WarrenBelz 153,034 Most Valuable Professional on at

Like (1)

Report
Hi CU15090428-0

You do not need the collection - you can use this in the Items of the gallery. I must admit I did not interpret your post requirements as requiring this (rather unusual) outcome.
SortByColumns( Table( Distinct( Ref, Subject_1 ), Distinct( Ref, Subject_2 ), Distinct( Ref, Subject_3 ) ), "Value", If( SortDescending1, SortOrder.Descending, SortOrder.Ascending ) )

Please click Does this answer your question 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 a Like.
MVP (Business Applications) Visit my blog Practical Power Apps Buy me a coffee

Was this reply helpful? Yes No