Duplicate Invoice No and Use Data from 2nd record

[Centaur]

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 dupes in a later step)

I need to grab the [GoodsReceipt] and put it in the first record (prior to deleting the dupes)

A little more detail:

I have a sort on [invoiceNo] and [Source]

[Source] = 1-PC or 2-Stampli (the 1 or 2 governs the sort)

the sort is ascending in both cases.

In every case the [GoodsReceipt] is in the [Source] = 2-Stampli (the 2nd record)

so it will be in the 2nd record.

How can I move the [Goods Receipt] to the first record? 

Noting this is only when there is a dupe on [Invoice No Stripped] AND [Invoice amount].  

here is a screen shot:

thank you and grateful for the help.

Let me know if it is not clear enough and I will provide more info.  

fwiw here are the first couple lines of code up to the sort 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"}),
#"Sorted Rows" = Table.Sort(#"Reordered Columns3",{{"Invoice No Stripped", Order.Ascending}, {"Source", Order.Ascending}}),

[Centaur]

Hi, ok thank you.  

Appreciate the response.  That is amazing. 

The first code block (the "copy") is the closest but it doesnt seem to move the "Goods Receipt" from the 2-Stampli (Source column) to the 1-PC row (its NULL).

The 2nd code block (the "move) seems to show all Goods Receipt as NULL.  

Below is the output (filtered for only 1 invoice) for the first code block (the "copy" Goods Receipt).

Possibly you see an easy tweak to the codes?  I am fine with the Goods Receipt being in both rows.  

thank you sir.  Appreciate the expert assistance. 

[ronrsnfld]

1. "I am fine with the Goods Receipt being in both rows." Does that mean that is your preferred outcome or not? You need to pick one or the other.

2. It works on your sample data as shown by my screenshots. So I would guess that your sample data is not truly representative of your real data.

3. In both your original question and also in your sample data, you state and show that "2-Simpli" is in the second row of each matching "invoice no/invoice amount", yet in your screen shot it is showing as the first row.

You also show only two (2) rows for any given matching in your sample data.

So if both those statements are true, I don't know why the order is reversed in your screenshot. Please explain.

[ronrsnfld · Accepted solution]

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.

[Centaur]

Hello ronrsnfld, excellent!  That worked perfectly.  Both of them did.  I am going to use the one that "MOVES" the goods receipt.   thank you again for your expert assistance and  hanging in with me.  You are obviously very talented.   have a good day. 

[ronrsnfld]

Yes, it adds to my confusion as to what you really want. Please provide a screenshot of your expected output from the data you presented in your sample workbook.

[Centaur]

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"

[Centaur]

Hi Ron, thank you for your response and clearly laid out steps. 
quick question: i need to group, i think, on BOTH invoice Number and Invoice Amount.  

Do I need to change anything?   Thank you again for the response.  

[ronrsnfld]

Again, in your question, you only mentioned duplicate invoice numbers. If there are other columns that need to be considered in determining what is a duplicate, then yes, you would add those to the grouping.

It does seem odd to me, though, to have a system which imputes two different "invoice amounts" to the same invoice number.

If you are going to combine different parts of the invoice so all the data can be presented in a single row, that can all be done withing the Table.Group aggregation section, without the need to separately delete duplicates.

[rohit1991]

Hi @Centaur ,

For each duplicate [InvoiceNo] (with matching [Invoice amount]), copy the [GoodsReceipt] from the [Source]=2-StampIt row to the [Source]=1-PC row.

Sort your table by [Invoice No Stripped], [Invoice amount], and [Source] (ascending so 1-PC comes before 2-StampIt). Group your table by [Invoice No Stripped] and [Invoice amount]. Inside each group, check if you have both 1-PC and 2-StampIt. If yes, take the [GoodsReceipt] value from the 2-StampIt row and update the 1-PC row with it. Expand back to a flat table and remove the “AllRows” helper column.

Here’s an example M script for the core part:

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]}
}),
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", {"Invoice No Stripped", "Source", "Invoice amount", "Goods Receipt"}, {"Invoice No Stripped", "Source", "Invoice amount", "Goods Receipt"}),
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"})

[Centaur]

wow Rohit, that is amazing.  I am sorry to say that it looks like I have an error in my first line of code ("RemovedDuplicates") after your last line ("RemoveHelper").  I did refer to the previous step ("RemoveHelper") however that did not fix it as there is still a "Token expected" error and higlights my first line of code ("RemovedDuplicates") after the ("RemoveHelper") line.  

Please see my entire code with your code.

I am showing a blank row to better offset your code (starting with "GroupedRows" and ending on "RemoveHelper". 

Do you happen to see the reason I have the expected token error on the "RemovedDuplicates" row?

NOTE:  I confirm if I remove all the code after your last line the token error is removed and no syntax errors either (and also changed the "In" line to refer to #RemoveHelper#) however I do have an error on the Expanded step once I step out of the Advanced Editor:

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]}}),
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", {"Invoice No Stripped", "Source", "Invoice amount", "Goods Receipt"}, {"Invoice No Stripped", "Source", "Invoice amount", "Goods Receipt"}),
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", "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"}})
in
    #"Renamed Columns1"

thank you very much!

Greatly appreciate your expert advice.  

[rohit1991]

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"

[Centaur]

HI Rohit, thank you very much for the response.  Ok I see where you pointed out I had missed a comma.  Argh!

I have pasted your revised code "as is".

It does look like I have an error here:

its on the following line:

Invoice #=nullable text, Currency=nullable text, Invoice date=nullable date,

I thought that the error might be because I do not have a field named "Invoice" but I dont think that is the reason (I changed it to one of the fields to no avail) .  I assume that line is similar to a "like" invoice.  

Do you happen to see where the issue is?  

Once again, I thank you very much for your help. 

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

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...