Forum Discussion
How to hide/remove duplicates based on condition
I'm guessing that it eliminated duplicates for each file individually but there were duplicates across files. I guess you do have to de-duplicate after combining the files then.
In this case, you use the same code (with any step references adjusted, e.g. #"Merge Columns" --> #"Inserted Merged Column") but put it at the end of the FreightForward v2 query rather than the Transform Files (2) function.
If you're going to use a database eventually, you might want to try approach #4 from my blog post.
I recently wrote a blog post on this:
Select Distinct Rows Ordered by Another Column -- Power Query Edition
AlexisOlson, thanks for sharing your post. Its a beast! Which method would you recommend for my case? My datasource is importing spreadsheets from sharepoint with each file containing an average of ~300k rows, current table rows at 3M+ rows.
I tried option 4 from your blog post but I'm getting an error "after merging with the step before the grouping". BA_Pete , mahoneypat any suggestions?
Appreciate any help please.
Link to file if you need
https://drive.google.com/drive/folders/1dmmbhdbzySZBr8oTxj9CWsmztwFQ3f8A?usp=sharing
Hi Anonymous ,
It looks like AlexisOlson is already helping you with this. You're in excellent hands. 👍
Pete
BA_Pete, no prob will wait for Alexis response. Thanks:)
I don't think query folding is relevant here, so I'd probably go with approach #2 or #3.
I tested #3 (the Table.Max(_, "Col") version) on a subset of your data loaded into an Excel file and it seems to work OK.
You could also try including the logic in your Transform File function like this (not tested):
let Source = (Parameter2 as binary) => let Source = Csv.Document(Parameter2,[Delimiter=",", Columns=66, Encoding=65001, QuoteStyle=QuoteStyle.None]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Grouped Rows" = Table.Group(#"Promoted Headers", {"JobID+Ticket"}, {{"Tbl", each Table.Max(_, "SEQUENCE"), type record}}), ColNames = List.RemoveItems(Table.ColumnNames(#"Promoted Headers"), {"JobID+Ticket"}), #"Expand Records" = Table.ExpandRecordColumn(#"Grouped Rows", "Tbl", ColNames, ColNames) in #"Expand Records" in SourceHi AlexisOlson , I inserted your logic in the 'Transform File' function with no issue, but then when I clicked on the actual table, the following error appears...
Fyi the [JobID+Ticket] column is a manual merge I created in the past.
Details: JobID+Ticket"
