User Profile
Cindysc1218
Copper Contributor
Joined Aug 26, 2021
User Widgets
Recent Discussions
Re: Excel and Power Query: PQ is taking hours to execute
I hope I understand you correctly. For your first suggestion, I filtered dates in regular Excel before using PQ. For your second suggestion, doesn't that mean every row in the first table has to join with every row in the second table in order to find out if the conditional is true or not?Re: Excel and Power Query: PQ is taking hours to execute
So that second step was something I had to research to find out how to do something I need. What I need to do is merge (left join) two tables using account and the dates as my primary keys. I understand I do an exact match on accounts to merge the tables, but I cannot do an exact match on dates which is why I am attempting to match when Ship Date is either within 30 days before or 30 days after the Post Date. After spending much time online, the code in step 2 appears to do what I need to do. Are you saying that your solution can do that the same thing?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, CindyRe: Excel and Power Query: Merge Using Most Recent Date
Riny_van_Eekelen Hi, Riny! Thank you for answering my question. It was good to know I was on the right track and I was able to take action that appears to be working the way I need it to (I've spent much time examining the results). I had done something similar to your method, but I had to make some adjustments that I am not sure why: Why is ChangeDate sorted in descending order? When removing duplicates, why choose to remove rows based on Date, Amt, and OwnerType instead of the entire row? How is that able to remove John from the 11/27/2020 transaction? 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.
