Combine only unique values in a query

Question

question

AlexandraGroza-0084 asked Nov 16, '20 Lz-3068 commented Nov 16, '20

Combine only unique values in a query

Hi,
I have a pretty large inventory table which is combined from multiple similar sources by user names used as unique key, but i have the following situation
User A - laptop laptop monitor
User B - monitor monitor monitor
User C - desktop desktop desktop laptop

I couldn't find any way of removing duplicates after i merged the table so i could have something like this:
User A - laptop monitor
User B - monitor
User C - desktop laptop

Can it be made directly in Powerquery or i have to work on the table imported in Excel with VB functions?

Thank you

power-query-not-supported

· 2

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Lz-3068 · Nov 16, 2020 at 12:20 PM

Hi @AlexandraGroza-0084

IThere's probably a Power Query solution to this but difficult to guide you with the info. you provided
Could you upload a couple of pictures showing how the tables look like before + after merging them as well as your query code? Ideally, if you could upload & share a workbook with a subset of your tables that would be even better

0 ·

AlexandraGroza-0084 Lz-3068 · Nov 16, 2020 at 12:53 PM

Thank you for the reply, but i cannot post any picture of the tables, as they contain corporate info, reason why i chose to explain the situation as simple as possible

The grouping formula is a standard one. Let me adapt it to the example in question

"Grouped Rows" = Table.Group(#"Replaced Value4", {"UserName"}, {{"ComputerNow", each Text.Combine([#"Computer@Home"]," - "), type text}})

Where table "Replaced Value4" looked something like this:
User A - laptop
User A - laptop
User A - monitor
User B - monitor
User B - monitor
User B - monitor
User C - desktop
User C - desktop
User C -
User C - desktop
User C - laptop

So i want to group the table using UserName as unique key but to show inly unique values on the second column. Ok, there was dash as delimiter in my formula too because i hoped i could split the column by distinct values after grouping

0 ·

Answer 1 · 2020-11-16T13:32:39.377Z

Lz-3068 answered Nov 16, '20 Lz-3068 commented Nov 16, '20

@AlexandraGroza-0084

Don't get me wrong but it takes less than 5 mins to create a table with dummy data in Excel. I had to do it and I hope it reflects what you have at step #"Replace Value4":

If the above picture reflects your reality:

     #"Grouped Rows" = Table.Group(#"Replaced Value4", {"UserName"},
         {
             "ComputerNow", each
                 Text.Combine(
                     List.Distinct([#"Computer@Home"],Comparer.OrdinalIgnoreCase),
                     " - "
                 ),
             type text
         }
     )

If this still not good please create a table with dummy data and upload it (a pic. should be enough).
On the other end if this solves your problem please mark this reply as answer to help others with a similar case - Thanks in advance

demo.png (8.5 KiB)

· 2

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AlexandraGroza-0084 · Nov 16, 2020 at 01:58 PM

Thank you very much.
Didn't think that having it displayed like you did really matter, but since it's the first question I ask here I will take you as example next time i have questions

List.Distinct was exactly what i was looking for

0 ·

Lz-3068 AlexandraGroza-0084 · Nov 16, 2020 at 02:58 PM

You're welcome, glad I could help
Not mandatory but pics/sample workbook & expected result usually avoid misunderstanding
Just in case PQ function doc. is here
Nice day...

1 ·

question