User Profile
Cindysc1218
Copper Contributor
Joined 4 years ago
User Widgets
Recent Discussions
Excel and Power Query: PQ is taking hours to execute
Hi, everyone: I am new to Power Query and not sure why it is taking hours to execute the code below. Because the data is sensitive, I am unable to share the file. However, I can say that the file contains 300K+ records, and the following are the PQ queries I am executing (note: variable names have been modified): = Table.NestedJoin(#"Transactions ", {"Account No."}, #"Orders", {"Acct"}, "Orders", JoinKind.LeftOuter) = Table.AddColumn(#"Transactions", "Merge by Date Range", each Table.SelectRows(#"Orders", (x) => x[Ship Date] >= [#"Post Date - 30"] and x[Ship Date] <= [#"Post Date + 30"] and x[#"Acct"] = [#"Account No."])) = Table.ExpandTableColumn(#"Added Custom", "Merge by Date Range", {"Acct", "Exp Type Name", "Product Description", "Units", "Retail", "Ship Date"}, {"Acct", "Exp Type Name", "Product Description", "Units", "Retail", "Ship Date"}) = Table.Group(#"Expanded Merge by Date Range", {"Acct", "Ship Date"}, {{"AllData", each _, type table [#"Post Date - 30"=nullable date, #”Post Date”=nullable date, #"Post Date + 30"=nullable date, #" Account No."=nullable text, Type=nullable text, Amount=nullable number, Acct=nullable text, Exp Type Name=nullable text, Product Description=nullable text, Units=nullable number, Retail=nullable number, Ship Date=nullable date]}, {"Sum of Retail", each List.Sum([Retail]), type nullable number}}) = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"Post Date - 30", "Post Date", "Post Date + 30", "Account No.", "Type", "Amount", "Acct", "Exp Type Name", "Product Description", "Units", "Retail", "Ship Date"}, {"Post Date - 30", "Post Date", "Post Date + 30", " Account No.", "Account Type", "Amount", "Acct.1", "Exp Type Name", "Product Description", "Units", "Retail", "Ship Date.1"}) = Table.RemoveColumns(#"Expanded AllData",{"Acct", "Ship Date"}) = Table.RenameColumns(#"Removed Columns",{{"Acct.1", "Account No."}, {"Ship Date.1", "Ship Date"}}) = Table.Sort(#"Renamed Columns",{{"Account No.", Order.Ascending}, {"Post Date", Order.Ascending}, {"Ship Date", Order.Ascending}, {"Retail", Order.Descending}}) I have since learned about Table.Buffer, so I applied it to every statement. But it has not helped. What else do I need to do to make this quicker? Thank you, CindyExcel and Power Query: Merge Using Most Recent Date
Hi, everyone: I was able to merge(inner join) a transaction table with an owner info table using account number as my key to get the following results: I need the transactions to be linked to the current owner(s) of the account. But as you can see, for the 9/16/2016 transaction, it is also linked to an owner who did not own the account until much later. Similarly, the 11/27/2020 transaction needs to be linked to the newer owner, so I am looking for something like this: Since the secondary owner does not change, Mary applies to both transactions. For other accounts, it is also possible for the primary owner to remain the same while the secondary owner has changed. There are also accounts in which there are up to 4 secondary owners. So, in short, I need the transaction dates to match up with the previous, most recent Owner Change Date for both primary and secondary owner(s). I am new to Power Query, so I do not know whether this is better done using PQ or simply Excel functions/formulas. Or maybe there are additional data manipulation/transformation steps I need to take before this...? Any input would be greatly appreciated. Thank you.
