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

[ronrsnfld]

Looking at only the data you present, why not just 

Table.FillUp(#"Previous_Step",{"Goods Receipt"})

You can also generate that step from the UI by selecting the column and then select FillUp from the dropdown menu.

If that doesn't do what you want, present a more comprehensive example.

[Centaur]

Hi Ronrsnfld,

that is a great idea.  If I could add a condition to it since I need to only fill up when there is a duplicate on [Invoice No Stripped] and [Invoice Amount].  If that can be done, I think it would work.  If you have an idea how that can be done I am all ears!  

thank you very much. 

[ronrsnfld]

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.

[Centaur]

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

[ronrsnfld]

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.

[Centaur]

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

[ronrsnfld]

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.

1. The default data types were not properly set. Ensure your Dates are dates and your Invoices are text.
2. You are grouping by "Invoice No Stripped" and "Source" so the Identical Invoices and Invoice amounts will never appear in the same subtable.
   
   1. 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

[Centaur]

HI ronrsnfld,

appreciate your response sir.  

1. 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. 

[ronrsnfld]

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.

[Centaur]

Hi Ronrsnfld,

I appreciate you hanging in there with me. 

I think I am making some progress.

My only question now is that after I group and expand the "count" column, the grouping seems to be undone meaning that I am relegated to seeing duplicates once again (grouping is removed).  I do note that prior to expanding the "count" column the duplicates seemed to have been removed, which is what I wanted however no other columns were showing other than the 2 I grouped by (Invoice No Stripped and Invoice amount).

Do you know how to recover the columns and keep the grouping after expanding the column?  

I searched online and seems a few others have had this issue but I couldnt quite figure out the answers.  

thank you very much. 

below please find the Grouping parameters I am using: 

here is what it looks like on the Group step (you can see all of the other columns are missing but if I expand the COUNT column then the Grouping is undone):

please find below the group line if needed:

= Table.Group(#"Sorted Rows", {"Invoice No Stripped", "Invoice amount"}, {{"Count", each _, 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, AppendCurr.Date=nullable date, AppendCurr.Curr=nullable text, AppendCurr.Rate=nullable number, DDNo=nullable text, USD Amount=number, Account=nullable text, TypeDraw=nullable text]}})

[ronrsnfld]

Your sample data does not have any entries where there are duplicate rows.

Yes, there are entries where the Amount and Stripped Invoice number match, but none where all the other entries are the same.

If you have that situation in your actual data, try:

Table.Distinct(Table.FillDown(Table.FillUp(_,{"Goods Receipt"}),{"Goods Receipt"})), type table[...

Again, please note that supplying a truly representative data sample early in this Q&A session might have resulted in a much more focused and quicker solution.

[Centaur]

HI Ronrsnfld,

The rows are not true duplicate rows...only on Invoice No Stripped and Invoice amount and this is detailed in the sample data.  I am needing to recover those columns that were removed by the Grouping.  

Does this change anything?  

here is a screen shot of the sample data.  Note the duplicates on Invoice No Stripped and Invoice amt. only (you can ignore the vendor column as its not impt)

just adding that I woudl need to fill up or down prior to the Grouping. 

[Centaur]

Hi Ronrsnfld,

sorry for any confusion. 

What I need to do:

1. Move the Goods Receipt to the row above

but only on a duplicate of Invoice No Stripped and Invoice amt.

I think a sort order needs to be done so that for each duplicate the Source column will show 1-PC on the top and 2-Stampli on the bottom.  I need to take the Goods REceipt from this 2-Stampli record and move it to the record above (in other words the record that is tagged with "1-PC"

then Group the data based on Invoice No Stripped and Invoice amt (this will remove the duplicates. 

let me know if I have missed something.

thank you sir. 

[ronrsnfld]

You have missed my request: "Please provide a screenshot of your expected output from the data you presented in your sample workbook."

All I see in your last message is an annotated screenshot of a single pair of lines of data from an original data set which is not in the data set you supplied in your sample workbook.

[Centaur]

Hello ronrsnfld,

Please see a screen shot of the sample data. 

I hope if makes sense.  Let me know if I need to provide more instruction. 

Grateful for the help sir. 

[ronrsnfld]

I don't know how to ask for what I need any more clearly. Perhaps there is a language problem here.

I asked for a screenshot of expected results from the data you provided in your sample workbook.

Instead, you provided a screenshot of your sample data marked up with arrows and comments that you had already shown in your sample data workbook. (I doubt that is how you want your results to look).

I would have expected you to post something like:

Or, since you wrote you wanted to "Move" the goods receipt number, perhaps:

or perhaps something else completely.

I seem unable to explain the term "screenshot of your expected results".

So no, what you posted still does not make sense to me, and I apparently am unable to explain properly exactly what I need from you nor understand your issues with regard to you not getting what you need. 

I was hopeful that if you posted a screenshot of what you expect, it might make things more clear, but I am unable to properly communicate to you that concept, so I do not think I will be able to help you further.

[Centaur]

Hello sir.  Sorry we are not making the progresss as expected.  You are correct that I want to move the Goods Receipt in the record above based on the conditions (Invoice No Stripped and Invoice amt).  Your screen shot above looks like what I am looking for.  Sorry I am not able to produce those screen shots in PQ as you have (instead posted a screen shot of the sample data with arrows) since my question to do that is exactly what you did in PQ but I dont know how to do that in PQ and is the underlying reason why I posted the question. 

If you can provide a solution I am ready.  

thank you again and sorry for the confusion. 

[ronrsnfld]

I cannot because I do not know what you want.

You mention "my screenshot above", but you don't even say which of the two screenshots!

I know you cannot produce what you want in PQ as that is what you are asking help for.

But there are other tools available to manually produce tables that should show your desired output from the input. Would be a lot easier (and faster) than our back and forth of 25+ questions, and take less time.

For example, you could take your sample data in Excel, copy it to a new sheet, then manually edit it to show what you want for a result.

[Centaur]

I am a little confused.  You were the one who posed a great idea to use fill up.  I gave you sample data as you wanted.  Its clear what I need and you clearly posted a pic of what I need as well as I.  We are going around in circles here.  I will post another question. 

[ronrsnfld]

Unfortunately, although it may be clear to you what you need, it is not clear to me.

You indicated that one of the two outputs I showed in a preceding post were what you wanted; but didn't indicate which one.

But here is the code for both:

My first screenshot: Copying "Goods Receipt" from last row to "1-PC" row:

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", each Table.FillUp(_,{"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"

My second screenshot: Moving the "Goods Receipt" to the "1-PC" row

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", each [a=Table.FillUp(_,{"Goods Receipt"}),
                        b=Table.ReplaceValue(
                            a,
                            each [Source],
                            null,
                            (x,y,z)=>if y<> "1-PC" then z else x,
                            {"Goods Receipt"}
                        )][b],  
            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"

It works on your sample data. However, it may not if your sample data is not truly representative of your actual data.