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.
Hi ronrsnfld,
Ok I have followed the steps (at least I think so).
I do seem to have an error though.
I am not sure if a comma needs to be after the end of the line
each Table.FillUp(_, {"Goods Receipt"}
I tried with an without a comma though but still ahve the Token Error.
I get an error here with the comma added:
If I leave the comma out then the error is here:
Do you happen to see where I am wrong?
here is the code without the comma (truncated after the RemovedDuplicates line):
let
Source = Table.Combine({StampliALLPaid, #"Project Costs"}),
#"Uppercased Text" = Table.TransformColumns(Source,{{"Invoice No Stripped", Text.Upper, type text}}),
#"Reordered Columns3" = Table.ReorderColumns(#"Uppercased Text",{"Processing Began", "Vendor", "WDDate", "Invoice #", "Invoice No Stripped", "Source", "Invoice amount", "Currency", "Invoice date", "Invoice due date", "Company Code", "Budget Category", "Invoice Status", "Goods Receipt", "Project ", "Funding Date", "DDNo", "USD Amount", "Account", "TypeDraw"}),
#"Grouped Rows" = Table.Group(#"Reordered Columns3", {"Invoice No Stripped", "Source"}, {{"Count", each Table.FillUp(_, {"Goods Receipt"} type table [Processing Began=nullable date, Vendor=text, WDDate=nullable date, #"Invoice #"=text, Invoice No Stripped=text, Source=nullable text, Invoice amount=nullable number, Currency=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, Funding Date=nullable date, DDNo=nullable text, USD Amount=nullable number, Account=nullable text, TypeDraw=any]}}),
#"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Invoice No Stripped", Order.Ascending}, {"Source", Order.Ascending}}),
#"Expanded Count" = Table.ExpandTableColumn(#"Sorted Rows", "Count", {"Invoice amount", "Goods Receipt"}, {"Count.Invoice amount", "Count.Goods Receipt"}),
#"Removed Duplicates" = Table.Distinct(#"Expanded Count", {"Invoice No Stripped", "Invoice amount"}),
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.
Hi Ronrsnfld,
Ok that was the issue. I added the paren to close the function and a comma after. I know its difficult not having the database to test.
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
here is the line now after making that change:
#"Grouped Rows" = Table.Group(#"Reordered Columns3", {"Invoice No Stripped", "Source"}, {{"Count", each Table.FillUp(_, {"Goods Receipt"}),
I might be doing something wrong but the [Goods Receipt] did not appear to move from the second record to the first. It is still sitting in the second record.
below is the entire code:
Do you see where the issue might be? If not, maybe I can send you some sample data.
let Source = Table.Combine({StampliALLPaid, #"Project Costs"}), #"Uppercased Text" = Table.TransformColumns(Source,{{"Invoice No Stripped", Text.Upper, type text}}), #"Reordered Columns3" = Table.ReorderColumns(#"Uppercased Text",{"Processing Began", "Vendor", "WDDate", "Invoice #", "Invoice No Stripped", "Source", "Invoice amount", "Currency", "Invoice date", "Invoice due date", "Company Code", "Budget Category", "Invoice Status", "Goods Receipt", "Project ", "Funding Date", "DDNo", "USD Amount", "Account", "TypeDraw"}), #"Grouped Rows" = Table.Group(#"Reordered Columns3", {"Invoice No Stripped", "Source"}, {{"Count", each Table.FillUp(_, {"Goods Receipt"}), type table [Processing Began=nullable date, Vendor=text, WDDate=nullable date, #"Invoice #"=text, Invoice No Stripped=text, Source=nullable text, Invoice amount=nullable number, Currency=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, Funding Date=nullable date, DDNo=nullable text, USD Amount=nullable number, Account=nullable text, TypeDraw=any]}}), #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Invoice No Stripped", Order.Ascending}, {"Source", Order.Ascending}}), #"Expanded Count" = Table.ExpandTableColumn(#"Sorted Rows", "Count", {"Invoice amount", "Goods Receipt"}, {"Count.Invoice amount", "Count.Goods Receipt"}), Custom2 = Table.Buffer(#"Expanded Count"), #"Removed Duplicates" = Table.Distinct(Custom2, {"Invoice No Stripped", "Invoice amount"}), #"Add Column" = Table.AddColumn(#"Removed Duplicates", "Custom", each if [WDDate] = null then [Invoice due date] else [WDDate]), #"Reordered Columns" = Table.ReorderColumns(#"Add Column",{"Processing Began", "Vendor", "WDDate", "Custom", "Invoice #", "Invoice No Stripped", "Invoice amount", "Currency", "Invoice date", "Invoice due date", "Company Code", "Budget Category", "Invoice Status", "Goods Receipt", "Project ", "Funding Date", "DDNo", "USD Amount", "Account", "TypeDraw"}), Custom1 = Table.AddColumn(#"Reordered Columns", "Custom2", each if [Custom] = null then [Funding Date] else [Custom]), #"Reordered Columns1" = Table.ReorderColumns(Custom1,{"Processing Began", "Vendor", "WDDate", "Custom2", "Custom", "Invoice #", "Invoice No Stripped", "Invoice amount", "Currency", "Invoice date", "Invoice due date", "Company Code", "Budget Category", "Invoice Status", "Goods Receipt", "Project ", "Funding Date", "DDNo", "USD Amount", "Account", "TypeDraw"}), #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns1",{"WDDate", "Custom"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom2", "WDDate"}}), #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"WDDate", type date}}), #"Reordered Columns2" = Table.ReorderColumns(#"Changed Type",{"Vendor", "WDDate", "Invoice #", "Invoice No Stripped", "Invoice amount", "Currency", "Invoice date", "Invoice due date", "Company Code", "Budget Category", "Invoice Status", "Goods Receipt", "Project ", "Funding Date", "DDNo", "USD Amount", "Account", "TypeDraw", "Processing Began"}), #"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns2",{{"WDDate", "WDDate_UseThis"}}), #"Reordered Columns4" = Table.ReorderColumns(#"Renamed Columns1",{"Vendor", "WDDate_UseThis", "Invoice #", "Source", "Invoice No Stripped", "Invoice amount", "Goods Receipt", "Currency", "Invoice date", "Invoice due date", "Company Code", "Budget Category", "Invoice Status", "Project ", "Funding Date", "DDNo", "USD Amount", "Account", "TypeDraw", "Processing Began"}) in #"Reordered Columns4"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.