Forum Discussion
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 ...
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.
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.
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.
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.
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.
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.