How do I implement supplementary data fields to an existing data set (design issue)

Mike L.
Mike L. ✭✭✭
edited 12/09/19 in Smartsheet Basics

I currently import a financial report each month into smartsheet.  As of now I have imported data from 2013 to present (72 files).  I need to further identify some financal transactions with addional data fields (category 1, category 2, category 3, expense type, project year etc).  These are data fields that are not imported with the original data.  

Do I edit each of the 72 imported data files and manually add additional fields?  Or is there a way to create a corresponding category sheet for each project then merge the transactions with the appropriate category data by use of a matching joining criteria (common field). 

I suppose I could go back and add fields to each file. But what if I find out later that I need another field?  Do I have to always go back and edit 72 files?  What if at the time I need the next field I have 144 files or 400 files? (this is likely because I get several reports monthly I'm only using the financial report for this example).  

Naturally I'm also looking for a quick fix for immediate deadlines.  I thought if I could run a report and capture transactions for a group of accounts that I could select all the report rows and copy them to a new sheet. 

This doesn't work you can't copy report rows to a new blank sheet.

Also you can't save a report as a sheet.  

I did export the report data to excel and import it as a new sheet.  Unfortunately I needed to correct all the data types because most "text/number" columns were made into dropdown lists from the import.

I will get this to work for my Monday deadline but it is not a process that I can manually sustain and expand on going forward.  

Any thoughts or suggestions for merging data (as opposed to appending).  The reason I think I might be able to something like this is because I was reading this article: http://radacad.com/append-vs-merge-in-power-bi-and-power-query

Thanks!

  

Comments