Mynda,
Here's another challenge I'm having working with my bank data. I need to be able to categorize the data by payee and am trying to set up a self-referencing query as I believe you have described in a blog post. I did the following steps but the categories are not sticking with the names when new data is added.
These were my steps:
1. Start with data in "Main" tab. Original data was the first 102 lines and did not include the "NEW PAYEE" line at the bottom.
2. Use the "Main" table to create a query on "Depositor/Payee" and output to "CatRef" tab, and add to data model
3. Add "Classification" column and categories for each row to "CatRef" table
4. Create a new query from the "CatRef" table including the "Classification" column, upload to data model as connection only
5. Merge the "CatRef" table with the newly created "CatRef 2" table, upload to data model as connection only
6. I then went back to the original "Main" table and added the "NEW PAYEE" line at the bottom to simulate adding more data.
7. I then refreshed the "CatRef" query. As I have highlighted in yellow, it inserted the "NEW PAYEE" in alphabetical order, but did not keep the categories with the original names. It should have left a blank space next to "NEW PAYEE" for me to then add the category.
Can you tell me what I'm doing wrong, or suggest a better way? Example worksheet attached.
Thank you,
Luciana
Hi Luciana,
If you add a column containing a formula, then this will work because the formula is the same all the way down the column, however you're adding text that differs from one row to the next, which is why you cannot get consistent results.
If you have Office 365 then you can use the UNIQUE function to extract a distinct list of Depositor/Payee names, which you can then assign a Classification to. The UNIQUE function won't sort the list, so any new Depositor/Payee names will be added to the bottom and easily identified. You can't put the UNIQUE function in an Excel Table, so you need to give it a dynamic named range, which you'll see in the name manager is 'Classifications'.
You can then get this data in the named range using the Excel.CurrentWorkbook function - see query 'DimClassifications'.
If you don't have Office 365 and the new UNIQUE Function then you can use this array formula.
Alternatively, you can use Power Query to extract the unique list (don't sort it) and then make a copy of that list so it's not connected to the query. In the copy, add the classification column and then get that table with Power Query and load to the Data Model.
With this last Power Query option, you will have to add any new Depositor/Payee names manually.
I hope that helps.
Mynda
