Forum Discussion

oliverL's avatar
oliverL
Frequent Visitor
5 years ago
Solved

Removing duplicates from table based on certain columns only

Hello,   I need help removing "duplicates" from a table. Let me ilustrate my problem with one example: Name Country Company Age City John Doe US Microsoft 30 Palo Alto John Doe U...
DAX
Help required
Power Query
  • CNENFRNL's avatar
    CNENFRNL
    5 years ago

    oliverL , Power Query is competent in such tasks. You might want to try,

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFNwyU9V0lEKDQYSvpnJRfnF+WklQLaxAZAISMzJV3DMKclXitXBVO5YVVqUClPqm5hSlJmCVZ17fn56DlyhZ1FqTmIeVGViXipMpTeySlMTIOGTn5eSnwdW6JKYp+CdmgdymG8EukstgERIfnZlPrpSHycg4V+UmAyxHaQuuERPwc9bKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Country = _t, Company = _t, Age = _t, City = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Name"}, {{"ar", each try _{[Company="Microsoft"]} otherwise _{0}}}),
    #"Expanded ar" = let cols = List.RemoveItems(Table.ColumnNames(Source),{"Name"}) in Table.ExpandRecordColumn(#"Grouped Rows", "ar", cols, cols)
in
    #"Expanded ar"

oliverL's avatar
oliverL
Frequent Visitor
5 years ago

Thanks CNENFRNL,


I have tried it and I think it works. I need to do some more testing since my real scanario is a bit more complex but I am sure I will be able to get to the solution with your help. I will mark it as solution. However, there are a couple of questions that I would like to ask you to see if you could be so glad to help me:

- In this section of the code:

#"Grouped Rows" = Table.Group(Source, {"Name"}, {{"ar", each try _{[Company="Microsoft"]}

Do you know why is not possible to replace the "=" in Company... with "<>"? In my real scenario I know what company I don't want. My bad for not making that clear in the original post.

 

-Also:

Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFNwyU9V0lEKDQYSvpnJRfnF+WklQLaxAZAISMzJV3DMKclXitXBVO5YVVqUClPqm5hSlJmCVZ17fn56DlyhZ1FqTmIeVGViXipMpTeySlMTIOGTn5eSnwdW6JKYp+CdmgdymG8EukstgERIfnZlPrpSHycg4V+UmAyxHaQuuERPwc9bKTYWAA==", BinaryEncoding.Base64)

 

How do you get the binary representation of a JSON file like that? When I load a JSON file I get the path to the container folder.


Thank you once again 😄

CNENFRNL's avatar
CNENFRNL
Community Champion
5 years ago

oliverL , further with your questions,

 

1. #"Grouped Rows" = Table.Group(Source, {"Name"}, {{"ar", each try _{[Company="Microsoft"]}

table{[colName1=value1, colName2=value2, ...]} is a fixed pattern for filtering a table down to a unique matching result (one and only one result). It's can be considered as syntactic sugar for Table.SelectRows(), I think. Only equality ("=") applies here.

 

2. when a dataset is pasted or manually entered into Power Query, it's automatically converted by PQ into a Json representation.

  • oliverL's avatar
    oliverL
    Frequent Visitor
    5 years ago

    Thank you CNENFRNL 

     

    You've really helped me a lot!