Duplicate Invoice No and Use Data from 2nd record

Hello Experts, I am going to try and explain simply. First of all I am a novice user of PQ. Not a programmer. I have a data set There are duplicates on [invoiceNo] This is ok (I delete ...

Need Help

Tips and Tricks

ronrsnfld

1 year ago

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.

ronrsnfld

Super User

1 year ago

Use the Table.GroupBy function, and create a custom aggregation to fill up that column.

Note:

Table.GroupBy will not necessarily preserve your sort order.
If that is an issue:
Work arounds: (after the sort and before the GroupBy)
1. Use Table.Buffer (preferred)
2. Add an Index column

Simple way of doing this:

Select the Invoice column and GroupBy
For "Operation" choose "All Rows"
Open the Advanced Editor
1. Look for a line, in the "Grouped Rows" step, that looks like:
  {"Count", each _, type table [...]}
2. Replace each _, with each Table.FillUp(_, {"Goods Receipt"}
3. Close the Advanced Editor
Re-Expand the table column.

Please note that had you posted a more comprehensive data sample, and/or included the information that only some lines had duplicate invoice numbers, I would have suggested this solution initially.

Centaur

Helper V

1 year ago

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"}),

Forum Discussion

Duplicate Invoice No and Use Data from 2nd record

Related content

Merge queries causing duplicate records

Eliminate duplicate records while keeping the most recent record.

Duplicate records when there are no duplicate records

Table Merge creates duplicate records

Duplicate ranking