Forum Discussion
Add Conditional Column (tag with 0 or 1)
1.) For future requests - allways provide sample data and expected resuld based on that!
2.) If you don't know how to do it - read note below my post.
3.) If you don't know how to use my query also read note below my post.
New sample: Removes duplicates in [Column1] and [Column2] and keep rows where there are no duplicates.
Before
After
let Source = Table.FromRows({{"A", 10, 1}, {"A", 10, 2}, {"B", 50, 5}, {"B", 50, 6}, {"C", 600, 8}, {"C", 600, 9}}), RemovedFirstDuplicate = Table.Combine(Table.Group(Source, {"Column1", "Column2"}, {{"T", each if Table.RowCount(_) = 1 then _ else Table.Skip(_), type table}})[T]) in RemovedFirstDuplicate
Hi Centaur1 ,
To keep the second instance of duplicates in Power Query, you can modify your query to tag each row within a duplicate group and filter based on the tag. Here's how you can do it.
First, ensure your table is sorted so duplicates are grouped together. You can use the Table.Sort function to sort by Invoice # and Invoice amount. Then, add an index column to uniquely identify each row. After that, group the table by Invoice # and Invoice amount. Within each group, add a new index column to tag rows with a "row number." This step helps identify the first, second, and subsequent duplicates. Finally, filter the table to keep only the rows with a "row number" of 2, corresponding to the second instance of each duplicate. Once done, you can clean up by removing unnecessary columns.
Here's the modified query:
let
Source = Table.Combine({#"FBL1N - 922A (no filter)_Link", #"Project Costs"}),
SortedRows = Table.Sort(Source, {{"Invoice #", Order.Ascending}, {"Invoice amount", Order.Ascending}}),
AddedIndex = Table.AddIndexColumn(SortedRows, "Index", 1, 1, Int64.Type),
GroupedRows = Table.Group(
AddedIndex,
{"Invoice #", "Invoice amount"},
{
{"All Data", each
let
AddSubIndex = Table.AddIndexColumn(_, "Row Number", 1, 1, Int64.Type)
in
AddSubIndex, type table}
}
),
ExpandedRows = Table.ExpandTableColumn(GroupedRows, "All Data", {"Index", "Row Number", "Vendor", "Funding Date", "WDDate", "Company Code", "Document Number", "Pay Yes/No", "Invoice date", "Posting Date", "Invoice due date", "Invoice No Stripped", "Payment Method", "Column16", "Vendor Code", "Payee/er", "Clearing Document", "Clearing date", "Text", "Part.bank type", "Payment Block", "Terms of Payment", "Sheet1", "DDNo", "Currency", "USD Amount", "Budget Category", "Account", "TypeDraw"}),
FilteredRows = Table.SelectRows(ExpandedRows, each [Row Number] = 2),
RemovedUnnecessaryColumns = Table.RemoveColumns(FilteredRows, {"Row Number"})
in
RemovedUnnecessaryColumns
This approach ensures you retain only the second occurrence of each duplicate based on Invoice # and Invoice amount. Let me know if you have any questions or need further assistance!
Best regards,
HI DataNinja, thank you for the response.
I am very novice to PQ.
sorry for my limited knowledge but I am not sure where I would place that code.
I assume that I would need to remove the "REMOVED DUPLICATES" line (#2 in the pic below)
Not sure if it matters but not all records are duplicates and not sure if the code takes that into account.
here is a pic of my code (25 lines). (the codes in text are in my first post if you happen to need them)
thank you very much.
Looking forward to your response.