Forum Discussion

Centaur's avatar
Centaur
Icon for Helper V rankHelper V
1 year ago
Solved

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's avatar
    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.

     

Centaur's avatar
Centaur
Icon for Helper V rankHelper V
1 year ago

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's avatar
ronrsnfld
Icon for Super User rankSuper User
1 year ago

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.