Forum Discussion
Data in two column matching
Solution is correct, However data is keep on loading more than 9 hours and data size reaches 22 GB so it is not feasible.
Can you please help me to apply excel formula in power query
IF(LEFT(NewColumn,2)="RE",COUNTIFS(NewColumn,SUBSTITUTE(NewColumn,"RE","ZA")),IF(LEFT(NewColumn,2)="ZA",COUNTIFS(NewColumn,SUBSTITUTE(NewColumn,"ZA","RE")),""))
Can any one please help. Why size is increase from MB to 22 GB on last step
#"Added Custom" = Table.AddColumn(step, "Custom", each let a=[Reference],
b=[Text],
c=Table.RowCount(Table.SelectRows(#"FinalTable",each [Reference]=a and [Text]=b and [MatchCheck]<>null))
in if c>0 then "Match" else null)
in
#"Added Custom"
Complete code is
let
Source = Excel.Workbook(File.Contents("C:\Users\KUNTALSINGH\Box\PepsiCo - NA Process Session\ACL_Input.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Scripts", type text}, {"Vendor", Int64.Type}, {"VendorName", type text}, {"BusinessArea", type any}, {"CompanyCode", type text}, {"FiscalYear", Int64.Type}, {"DocumentType", type text}, {"DocumentDate", type date}, {"PostingKey", Int64.Type}, {"PostingDate", type date}, {"DocumentNumber", Int64.Type}, {"Reference", type text}, {"Amountindoccurr", type number}, {"DocumentCurrency", type text}, {"Amountinlocalcurr", type number}, {"LocalCurrency", type text}, {"ClearingDocument", Int64.Type}, {"ClearingDate", type text}, {"Text", type text}, {"NetDueDate", type text}}),
#"Filtered Rows2" = Table.SelectRows(#"Changed Type", each ([DocumentType] = "RE" or [DocumentType] = "ZA")),
MergedTables = Table.NestedJoin(#"Filtered Rows2", {"Text"}, #"Filtered Rows2", {"Reference"}, "NewColumn", JoinKind.FullOuter),
ExpandedTables = Table.ExpandTableColumn(MergedTables, "NewColumn", {"DocumentType", "Reference", "Text"}, {"DocumentType1", "Reference1", "Text1"}),
CustomColumn = Table.AddColumn(ExpandedTables, "MatchCheck", each if [DocumentType] = "ZA" and [DocumentType1] = "RE" and [Text] = [Reference1] then "Match"else if [DocumentType1] = "ZA" and [DocumentType] = "RE" and [Text1] = [Reference] then "Match"
else null),
#"FinalTable" = Table.RemoveColumns(CustomColumn,{"DocumentType1", "Reference1", "Text1"}),
#"Filtered Rows" = Table.SelectRows(#"FinalTable", each [Scripts] <> null),
step = Table.Buffer( #"Filtered Rows" ),
#"Added Custom" = Table.AddColumn(step, "Custom", each let a=[Reference],
b=[Text],
c=Table.RowCount(Table.SelectRows(#"FinalTable",each [Reference]=a and [Text]=b and [MatchCheck]<>null))
in if c>0 then "Match" else null)
in
#"Added Custom"