Forum Discussion

Centaur's avatar
Centaur
Helper 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
Helper V
1 year ago

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

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.