Forum Replies Created
-
Posts
-
Forgot to add that the presence of ‘today’, ‘yesterday’ depends upon the date range specified. If the exported file is limited to a single day, then all of the timestamps will have ‘today’ in them.
It’s not practical to guarantee that the file will be exported every single day, so on some days it will be exported for a past range of dates. For example, if the person exporting forgets or is out, it might be days before we remember and then we’ll need to catch up for the missed days. The exported file would of course have a time/datestamp which might be useful in deducing what day ‘today’ is but that might not be reliable.
Perhaps the most reliable thing to do would be to always have a date range of >2 days, such that rows with ‘today’ and ‘yesterday’ can be ignored momentarily while real dates can then be sorted for most recent, and then ‘yesterday’ could be +1 day and ‘today’ +2 day after most recent.
My laptop has 8GB ram and ample paging space (200GB SSD about 50% full)
My spreadsheets are small, ranging from 300 KB to 1.5MB
The most recent crash was probably caused by me mocking up a dashboard and just copy/pasting a single chart about 30 times (5 users, 6 metrics). That time prior to the crash Excel stopped being able to save the file, no matter my trying different locations, file types, etc. There were no images in that spreadsheet and the pivotchart was created in excel without using power query or powerpivot at all. But this fail was an oddball. In general it seems that if I leave Excel open overnight and just pick up where I was I end up with it terminating and auto restarting, so I’ve gotten in the habit of closing out all windows and restarting each new session.
Interesting … So I guess you had instabilities with 32-bit that got better with 64-bit? I’m getting crashes every hour or so, have gotten extremely careful to save early and often 😉
I do have Office 365 Pro Plus, as does everyone in our agency, and it sounds like I’m going to have to switch to the 64-bit version for it and Power Pivot.
I made great progress today, now have at least a skin-deep understanding of M language features which I used to do more field parsing for constructs that start with ID-STATE, where ID = END; CSO; QUO; QUOX and STATE varies for each ID type, such as END-NEW; END-SNT; END-PND; END-RVW; END-FUP; END-FUPFIN, etc. Separating these into two new columns gave me exposure to a variety of text functions and If then else, and was complicated by the fact that these are all human entered so the data cleanliness is pretty poor. My powers are growing!
Next up is figuring out how to create pretty dashboards and reports and importantly how to share them.
Am I correct in assuming that if I stay within Excel & Power Pivot, I can share an excel workbook created with Power Query and Power Pivot on a 64-bit platform with one or more users who don’t have Power QUery and are on 32-bit? They would only be able to view the dashboard, not refresh data or modify anything.
I listened to AVI’s pitch the other day on PowerBI.com and it seems that the world hasn’t evolved enough yet to allow my PowerPivot to be a data connection to PowerBI.com, right? So if I were to switch to use PowerBI.com I would be jumping to a parallel universe which more of some features and less of others, right?
After adding the rest of the steps you demonstrated in the example file, most importantly the step that selected a subset of rows using Table.SelectRows, I end up with about 52K rows that do load. But this makes me think that I will eventually have some difficulty somewhere down the road with 32-bit Excel.
Anyway, lots of progress so far, now have datedim (from Marco) and time dim (from http://ginameronek.com/2014/10/01/its-just-a-matter-of-time-power-bi-date-time-dimension-toolkit/) and things are looking good.
Tom,
Finally got time to work on this with my own dataset and ran into a few issues…
a) wasn’t obvious to me how to create the third query which performed the Table.Join. I I knew that I could do it manually by creating any query and then editing in the advanced editor, but I thought that joining two existing query tables should work by clicking. I tried using the combine/Merge but that did something other than Table.Join. So my question is how best to create the query that does the join. I’m past this because I ended up manually editing the m language which of course worked.
NOTE – For question b) below, right after sending I realized that I had just done the table.join but not the reduction, so what was being loaded to Power Pivot was **way** larger than it needed to be. Assuming that Power Query can handle this I’ll probably be fine. But I’m still curious about whether you think 32-bit will be an issue.
b) My dataset is about 64K rows, and with about 40 different activity types there would be 2.4M rows. The data started loading but I eventually got an error saying that I should consider 64-bit Excel. I might have to just do that, but my existing office laptop (T540P) was running 32-bit MS Office and I think 32-bit Win8.1 as well, haven’t thought about it in awhile. Should a 2.4M row dataset with about 7 columns work? My ultimate use case will have much larger datasets.
Thanks so much Tom. I can see the calculated columns and that there is much awesome-ness in only a few lines. Thanks for showing it in a working example too!
By the way, I did read M is for (data) Monkey, got a lot out of it, and tonight (hopefully) I can tackle the newly released v2 of Rob Collie’s book.
I’m getting very excited about being able to start putting useful data together, so I’ll likely be back here with more (and harder ;-)) questions
Doable is great … hopefully it’s not overly complex 😉
It would be great to get a psuedo code overview to get me started in the right direction, but just knowing its doable is helpful.
It is sufficient to know that any salesperson has started conversations with the customer. The viewpoint I have is from the household, as it is represented by the phone number. Knowing that the household has been entered into the sales pipeline tells me that we have a sales opportunity. If there are multiple sales people calling that’s okay, it could be one is helping the other in some way.
60 days would be fine, it’s just an arbitrary time that might change.
By the way, there is another interesting aspect to associating calls with households, because households often have(two adults with sometimes home, cell1 and cell2 phone numbers
Tom,
You have my heartfelt thanks for taking the time to spell things out so clearly. I had the misfortune to stumble upon this non-vanilla problem with my first foray into Power Pivot (and now Power Query) but you’ve given me lots to ponder, and I very much appreciate the suggestion to read “M is for (Data) Monkey” … will be starting that this evening 😉
Thanks!
Tom,
When I open the attached prep file I can see errors because associated .txt files aren’t present in the expected locations for each of three queries. I presume this is because you established one input file for the activity types and another for the activities (ie: fact table). And then the 3rd must have been an output .txt for the result?
In the Advanced Editor I can see M language code for each of the three different queries. Were these completely hand-written by you? Or was some/all of the code resulting from menu/buttons in Power Query. I’m asking so I can determine whether I need to be looking at Power Query M language much. At this moment I don’t even know what a comment character is in M language 😉
I can experiment, but it would help me to know the broad steps I need to get the Prep worksheet functioning so I can see how it operates. I tried the obvious step of editing the Activity Types Query to point at my local workbook, but the Query Editor window then displays something I’m not expecting (a sheet reference and something about a filter …) and when I close the Query Editor and hover over the ‘connection only’ query at the right side pane instead of seeing a list of the activity types I see the same kind of information (sheet reference, filter, etc)
By the way, I was able to download a PowerQueryExample.xlsx from Chris Webb’s blog and had similar issues with disconnected source files, but once I added the two supporting workbooks and edit the query settings to point to them that example seems to work. So I suspect I’m just missing something simple in getting your Prep file working.
Thanks so much for giving me the links and the workbook. Diving into it now!
… the dataset is currently at 65K rows with a timeframe of 2015 YTD. I have data going back to 2010, so conservatively the dataset could be in the range of 300-400K rows.
The chance of an example file is very good 😉 See attached
I have no control over the merging of Customer Name, Activity Type, and Subject in the Activity column now, and very unlikely in the future. It comes from a legacy application and I have no influence on those responsible.
But yes, you pointed out some rules I didn’t mention.
- if there is a customer name, it’s always on the left side of the activity type
- if there is a subject, there is always a customer, and the subject is to the right
- there is always an activity type
The reason for their being a subject in some cases is that the activity represents something that has a subject line, such as an email or a ToDo where a staff person chooses to add a subject. But it’s not required.
The attached file has two tabs:
Activity Report shows 21 example rows.
Type shows all of the possible activity types that I’ve been able to discoverIt’s possible there will be more activity types, but it’s not critical that I can match against all activity types, just the interesting ones.
I’m using Excel 2013 MS Office 365 Pro Plus, and all of my data files at this point are exported Excel files from various tools like agency management systems, lead management systems, and voip phone systems.
The end goal is to be using all these sources of data to establish dashboards for daily activities that happen in an insurance agency.
