Forum Discussion
Duplicate Invoice No and Use Data from 2nd record
Here is another pair of code blocks that might work on what you have:
to Copy
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{ {"Processing Began", type date}, {"Vendor", type text}, {"WDDate", type date}, {"Invoice #", type text}, {"Invoice No Stripped", type text}, {"Invoice amount", type number}, {"Currency", type text}, {"Invoice date", type date}, {"Invoice due date", type date}, {"Company Code", type text}, {"Budget Category", type text}, {"Invoice Status", type text}, {"Goods Receipt", Int64.Type}, {"Project ", type text}, {"Source", type text}, {"Funding Date", type any}, {"DDNo", type any}, {"USD Amount", type any}, {"Account", type any}, {"TypeDraw", type any}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Invoice No Stripped", "Invoice amount"}, { {"Count", (t)=>Table.ReplaceValue( t, List.RemoveNulls(List.Distinct(t[Goods Receipt])){0}?, null, (x,y,z)=>y, {"Goods Receipt"} ), type table [Processing Began=nullable date, Vendor=nullable text, WDDate=nullable date, #"Invoice #"=nullable text, Invoice No Stripped=nullable text, Invoice amount=nullable number, Currency=nullable text, Invoice date=nullable date, Invoice due date=nullable date, Company Code=nullable text, Budget Category=nullable text, Invoice Status=nullable text, Goods Receipt=nullable number, #"Project "=nullable text, Source=nullable text, Funding Date=nullable text, DDNo=nullable text, USD Amount=nullable text, Account=nullable text, TypeDraw=nullable text]}}), #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Invoice No Stripped", "Invoice amount"}), #"Expanded Count" = Table.ExpandTableColumn(#"Removed Columns", "Count", {"Processing Began", "Vendor", "WDDate", "Invoice #", "Invoice No Stripped", "Invoice amount", "Currency", "Invoice date", "Invoice due date", "Company Code", "Budget Category", "Invoice Status", "Goods Receipt", "Project ", "Source", "Funding Date", "DDNo", "USD Amount", "Account", "TypeDraw"}) in #"Expanded Count"to Move to 1-PC
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{ {"Processing Began", type date}, {"Vendor", type text}, {"WDDate", type date}, {"Invoice #", type text}, {"Invoice No Stripped", type text}, {"Invoice amount", type number}, {"Currency", type text}, {"Invoice date", type date}, {"Invoice due date", type date}, {"Company Code", type text}, {"Budget Category", type text}, {"Invoice Status", type text}, {"Goods Receipt", Int64.Type}, {"Project ", type text}, {"Source", type text}, {"Funding Date", type any}, {"DDNo", type any}, {"USD Amount", type any}, {"Account", type any}, {"TypeDraw", type any}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Invoice No Stripped", "Invoice amount"}, { {"Count", (t)=>Table.ReplaceValue( t, each [Source], List.RemoveNulls(List.Distinct(t[Goods Receipt])){0}?, (x,y,z)=> if y = "1-PC" then z else null, {"Goods Receipt"} ), type table [Processing Began=nullable date, Vendor=nullable text, WDDate=nullable date, #"Invoice #"=nullable text, Invoice No Stripped=nullable text, Invoice amount=nullable number, Currency=nullable text, Invoice date=nullable date, Invoice due date=nullable date, Company Code=nullable text, Budget Category=nullable text, Invoice Status=nullable text, Goods Receipt=nullable number, #"Project "=nullable text, Source=nullable text, Funding Date=nullable text, DDNo=nullable text, USD Amount=nullable text, Account=nullable text, TypeDraw=nullable text]}}), #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Invoice No Stripped", "Invoice amount"}), #"Expanded Count" = Table.ExpandTableColumn(#"Removed Columns", "Count", {"Processing Began", "Vendor", "WDDate", "Invoice #", "Invoice No Stripped", "Invoice amount", "Currency", "Invoice date", "Invoice due date", "Company Code", "Budget Category", "Invoice Status", "Goods Receipt", "Project ", "Source", "Funding Date", "DDNo", "USD Amount", "Account", "TypeDraw"}) in #"Expanded Count"Again both of the above work without a problem on the sample data you provided.
It appears you are missing a parenthesis to close the function:
Table.FillUp(_, {"Goods Receipt"}), type table ...
It was not in my example, either. But since I had no data to test it against, I missed it.
In case you missed the post with my file. I revised my reply today to add a link to the sample data.
Here is a link to some sample data (I hope it works. I tested and it does):
https://1drv.ms/x/c/501a06dcdb8cc24b/EYLKLUSLZZdPksh1Kqw3KYwBW7lw0rjOmtVjr5KRIY9bSQ?e=CsYDYA
I've been out of town for a few days so couldn't respond sooner.
But:
Some problems with your code, applied to your sample data.
The only thing I changed initially was the Source line to reflect the different source on my worksheet.
- The default data types were not properly set. Ensure your Dates are dates and your Invoices are text.
- You are grouping by "Invoice No Stripped" and "Source" so the Identical Invoices and Invoice amounts will never appear in the same subtable.
- You can easily see this if you select one of the `Table` cells in the Grouped column.
I stopped at that point as your next "Sorted Rows" column doesn't apply since you shouldn't be grouping by Source.
Note that you've never shown what you want to do with respect to the unmatched columns with invoices that match by number and amount. In other words, Matches of Invoice/amount usually have different Sources.
Ron
HI ronrsnfld,
appreciate your response sir.
- The default data types were not properly set. Ensure your Dates are dates and your Invoices are text.
===>I think you are referring to the sample data? Yes, I can see they were not properly set in the sample data. I can however confirm in the my production data they are dates and text.
<Note that you've never shown what you want to do with respect to the unmatched columns with invoices that match by number and amount. In other words, Matches of Invoice/amount usually have different Sources.
====>For those records, they would not be removed since there is not a match. I am combining 2 records sets and deleting the dupes but keeping the ones that are not duplicated.
Was the issue on the Grouping knowing that my source data is properly formatted Dates Date and Invoice No Stripped as Text?
thank you very much. Have a good weekend.
No, the issue was that you included the "Source" column in your grouping when you should be grouping either by "Invoice number" alone, or possibly "Invoice Number and Amount", depending on what you really want to do.