Duplicate Invoice No and Use Data from 2nd record

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

Note:

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

 

Simple way of doing this:

1.    Select the Invoice column and GroupBy
2.    For "Operation" choose "All Rows"
3.    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
4. 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.

Hi Centaur ,

You're very close! The issue you're running into—"Token expected"—is due to a missing comma at the end of the previous step. In Power Query M code, each step must be separated by a comma, except for the final one before the in keyword.

In your code, you ended the RemoveHelper step like this:

RemoveHelper = Table.RemoveColumns(UpdatedGR, {"GoodsReceiptToKeep", "Goods Receipt"})

 

But then immediately started the next step with:

#"Removed Duplicates" = Table.Distinct(#"RemoveHelper", {"Invoice No Stripped", "Invoice amount"}),

Since "Removed Duplicates" is technically the next step after RemoveHelper, you need to put a comma at the end of the RemoveHelper line to separate the steps properly. Here’s the corrected version of that section:

RemoveHelper = Table.RemoveColumns(UpdatedGR, {"GoodsReceiptToKeep", "Goods Receipt"}),  
#"Removed Duplicates" = Table.Distinct(RemoveHelper, {"Invoice No Stripped", "Invoice amount"}),

 

Also, you don't need the #"" syntax for RemoveHelper unless it includes spaces or special characters—RemoveHelper works fine as a reference on its own, and using #"RemoveHelper" can sometimes cause confusion if it was never defined with quotes.

 

Here is what i believe would solve your issue:

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

    #"Sorted Rows" = Table.Sort(#"Reordered Columns3",{
        {"Invoice No Stripped", Order.Ascending}, 
        {"Source", Order.Ascending}}),

    GroupedRows = Table.Group(#"Sorted Rows", {"Invoice No Stripped", "Invoice amount"}, {
        {"AllData", each _, type table [
            Invoice No Stripped=nullable text, 
            Source=nullable text, 
            Invoice amount=nullable number, 
            Goods Receipt=nullable text,
            Processing Began=nullable datetime, Vendor=nullable text, WDDate=nullable date, 
            Invoice #=nullable text, Currency=nullable text, Invoice date=nullable date, 
            Invoice due date=nullable date, Company Code=nullable text, Budget Category=nullable text, 
            Invoice Status=nullable text, Project = nullable text, Funding Date=nullable date, 
            DDNo=nullable text, USD Amount=nullable number, Account=nullable text, TypeDraw=nullable text
        ]}
    }),

    AddGRColumn = Table.AddColumn(GroupedRows, "GoodsReceiptToKeep", each 
        let
            groupTable = [AllData],
            grValue = try Record.Field(
                Table.SelectRows(groupTable, each Text.Contains([Source], "Stampli")){0}, 
                "Goods Receipt"
            ) otherwise null
        in grValue
    ),

    Expanded = Table.ExpandTableColumn(AddGRColumn, "AllData", {
        "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"
    }),

    UpdatedGR = Table.AddColumn(Expanded, "Final Goods Receipt", each 
        if Text.Contains([Source], "1-PC") then [GoodsReceiptToKeep] else [Goods Receipt]
    ),

    RemoveHelper = Table.RemoveColumns(UpdatedGR, {"GoodsReceiptToKeep", "Goods Receipt"}),

    #"Removed Duplicates" = Table.Distinct(RemoveHelper, {"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", "Final 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", "Final 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", 
        "Final Goods Receipt", "Project ", "Funding Date", "DDNo", "USD Amount", "Account", 
        "TypeDraw", "Processing Began"
    }),

    #"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns2",{{"WDDate", "WDDate_UseThis"}})
in
    #"Renamed Columns1"