Forum Discussion
How to Remove Duplicates in Calculated Table?
Hi Anonymous
When i append two tables in Query editor, tehn remove duplicates rows for the "Product code" column, it returns distinct "Product code", "description" and "department".
Would you like this result?If not, please let me know.Best Regards
MaggieCommunity Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous
Do you have tables as below:
Finally, you want a table with distinct product code as below, right?
If so, please open edit queries, add a custom column in three tables
Then create a new blank query
let
Source1 = Table.SelectColumns(Table1,{"new product code","product name"}),
Source2 = Table.SelectColumns(Table2,{"new product code","product name"}),
Source3 = Table.SelectColumns(Table3,{"new product code","product name"}),
#"new table"=Table.Combine({Source1,Source2,Source3}),
#"Removed Duplicates" = Table.Distinct(#"new table", {"new product code"})
in
#"Removed Duplicates"
Finally, close&&apply.
Maggie
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Maggie
Thank you for the reply. It's not a unique product code that I need but a unique description. Example I have is:
Table 1 Table 2 Product Code Description Department Product Code Description Department 1 Red External 1 Red Extn'l 2 Green External 2 Gr'n External 3 Blue Internal 3 Blue Internal If I join the tables, even if I wrap them in UNIQUE, I get two entries for product 2, one called 'Green' and one called 'Gr'n' so i can't lookup to the new table.
The product is the same, it's just called two slightly different things in two different environments. I understand why wrapping UNION in UNIQUE repeats product 2 (becasue it's looking for unique rows) but I need unique to look at the product code only and return either/any of the descriptions as long as it's consistent.
I've tried summarize, but I can't find a way to 'summarize' a text filed to return first/last/any single entry.
Thanks
N
Hi Anonymous
When i append two tables in Query editor, tehn remove duplicates rows for the "Product code" column, it returns distinct "Product code", "description" and "department".
Would you like this result?If not, please let me know.Best Regards
MaggieCommunity Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Thanks Maggie! I don't know why I'd not considered appending the actual queries! Works perfectly :)