locked
Tagging Excel 2013 rows RRS feed

  • Question

  • Hi,

    Background
    * I am trying to learn (by doing) the latest Microsoft BI features
    * I am very comfortable with SQL Server and its BI stack (SSSI, SSAS, SSRS), but new to Excel 2013, PowerPivot, DAX, ...

    For my learning project, I am downloading my bank account transactions in CSV format. Using Excel, I would like to add multiple tags to each transactions so that I can create PowerPivot, PowerView and SSRS reports to slice and dice the data.

    The CSV format is {Date,Description,Amount,Balance} e.g.:

    20150501,Amazon,34.99,2009.34     

    I want to use Excel as my source data and a GUI for adding tags. Conceptually, I would like something like:

    20150501,Amazon,34.99,2009.34,gadgets,amazon,photography

    i.e. 3 tags {gadgets,amazon,photography} to be associated with a transaction.

    Obviously this means a many-to-many relationship between transactions and tags which would be handled using a bridge table in SQL Server.

    What is the best way of implementing this in Excel 2013?



    • Edited by amir tohidi Friday, June 12, 2015 11:53 AM Added more info
    • Moved by L.Hl Tuesday, June 16, 2015 9:47 AM
    Friday, June 12, 2015 11:48 AM

Answers

  • Hi Michael,

    I couldn't find a way in Excel for many-to-many relationships so I have abandoned Excel and implemented everything in SQL Server.

    • Marked as answer by amir tohidi Thursday, September 3, 2015 8:21 AM
    Thursday, September 3, 2015 8:21 AM

All replies

  • Hi,

    Based on the description, you are going to add tags for each record in the csv file, since you could get the tags  { gadgets,amazon,photography} handled using a bridge table in SQL Server., is there any other common fields that the sql table and the csv file shared , like primary key, transaction id ? if it is , you may build a connection by these fields.

    Hope this could help you

    Best Regards

    Lan


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, June 15, 2015 3:35 AM
  • Hi Lan,

    I am currently trying to focus on Excel, PowerPivot and PowerView and PowerBI only, so I am keeping SQL Server out of this. 

    Not knowing Excel's advanced features, I am not sure which of the following is best practice within the Excel world:

    1. One Tag column with comma separated tags 
    2. Multiple tag columns (Tag1, Tag2, ..., TagN) where each column would hold one, and only one tag
    3. Separate worksheet with all possible tags somehow (?) linked to the transactions rows in the transactions worksheet
    4. A Data Model with three tables: Transactions, Tags and TransactionTagsBridge
    5. Any other feature that I am not aware of???

    1-4 each have their advantages and disadvantages. I am not sure which, if any, is considered best practice for this particular requirement/app: banking transactions being stored and tagged in Excel, using Excel as a database and a GUI for the "app")



    • Edited by amir tohidi Monday, June 15, 2015 4:01 AM format
    Monday, June 15, 2015 3:58 AM
  • Hi,

    This is the forum for Excel development (VBA, Customization), for your question is about BI features in Excel, I will move this thread to forum for PowerPivot for Excel, you will get more professional and effective help there.

    Thanks for your understanding.

    Best Regards,

    Lan


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, June 16, 2015 9:46 AM
  • Hi Amir,

    Are you still looking for help with this?


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Thursday, August 13, 2015 9:27 PM
  • Hi Michael,

    I couldn't find a way in Excel for many-to-many relationships so I have abandoned Excel and implemented everything in SQL Server.

    • Marked as answer by amir tohidi Thursday, September 3, 2015 8:21 AM
    Thursday, September 3, 2015 8:21 AM