Forum Discussion
Working on project that requires me to filter rows blank rows
Perhaps Power Query will do the job, but it's better to discuss on concrete sample.
SergeiBaklan Thank you for the prompt reply. I gave that a shot, but did not have much success. To give more clarity:
I have two spread sheets. Spreadsheet 1 are leads exported from a CRM, and one column (column v) contains a list of names of potential reps that can service this lead. Spreadsheet 2 contains the list of reps that already have this lead. My goal is to filter out the reps that already have these leads so that I may share the leads only with the people who do not have them already.
I tried creating an advanced filter in Power Query, but did not have much success. I manually entered all the reps I have from Spreadsheet 2 into an advanced filter and set as "Or" "Does Not Contain" with the intention of only seeing leads that do not contain a rep that already has the lead.
Edit: I switched the to "And" and now it's filtering properly. Follow up question: There are entries in Spreadsheet 1 that have their column v field blank, and these get filtered out... is there any way to include them in my final results?
Sorry, I didn't catch what do you mean under advance filter in Power Query and what are your steps exactly.
As I see:
- you have raw source data imported from CRM in form of Table or named range
- you query it using From Table/Range connector
- in opened Power Query editor you filter on on of the column column
- you return result back in Excel sheet into the new table
Right. I've done a couple things since my last post so I'll go through my steps:
1. Export leads from CRM and open in Excel.
2. Separate dealers column into 3 columns. When I export the leads, this field is populated as so: "John, Jacob, Jingle" so I separated them into 3 columns "Dealer 1 / Dealer 2 / Dealer 3"
3. I click Data > Get Data > From Table/Range
4. In here, I have 3 queries created.
Query 1: Source > Changed Type > Filtered Rows (This filters dealers out of the row that have received the lead already. To do this, I created a filter for the row as follows: Column: Dealer 1 "does not contain" John AND Dealer 1 "does not contain" Jacob, and so on for 100 dealers)
Query 2: Source > Changed Type > Filtered Rows (This filters dealers out of the row that have received the lead already. To do this, I created a filter for the row as follows: Column: Dealer 2 "does not contain" John AND Dealer 2 "does not contain" Jacob, and so on for 100 dealers)
Query 3: Source > Changed Type > Filtered Rows (This filters dealers out of the row that have received the lead already. To do this, I created a filter for the row as follows: Column: Dealer 3 "does not contain" John AND Dealer 3 "does not contain" Jacob, and so on for 100 dealers)
5. Now I have 3 different sheets for each filter, and I do not know which is most accurate.
Remember, the list of 100 dealers that already have these leads. My goal is to filter these dealers out so I can inform those who have not received the leads.
If I understood correctly you'd like to extract names of dealers who doesn't have leads, like this
If so you may query left (source) table, select Lead column, unpivot other columns and keep only Lead and Value.
Refer to this query, keep only names value, sort and remove duplicates - you have here list of all names
Refer to first query, group by leads, and in formula bar change each _, table... on each _ [Value] to keep only names for each lead. Add custom column with formula =Text.Combine(List.Difference(Names[Value],[Names]),", "), here we have difference of all names with names who have this lead. Keep only Lead and this column.
If necessary you may split above custom column on columns using ", " as delimiter.
You may check all steps in attached file.