Forum Discussion
Working on project that requires me to filter rows blank rows
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.
- I am not understanding your directions. I split the columns using "," as a delimiter, that is how I accomplished Step 2 from my previous post.
My goal is to filter out all leads with dealers that have not been notified. That is why I have the filters set up as "does not contain".Let forget about formulas for a while. Source and result are as in my sample, or it shall be something different? If different, could you please provide the sample, as simple as my one, just to illustrate the task.
I think I understand what I need to do now. I split the columns and then unpivoted the 3 columns, as per your suggestion. Now, I should be able to apply a filter for the 100 dealers across the Value column and only be left with the dealers that have not received the leads.
Is this correct? If so, I have the list of dealers in another spreadsheet. Can I use that to filter them out without having to type them all in individually using the Filter function in Power Query?