...
Power Query is a free download from Microsoft for Office 2010 and 2013 running on Windows 7 plus. I am running Office 2010 on Windows 7 64 bit 64bit at the moment and it is working great.
...
| Panel |
|---|
Power BI for Office 365 and Excel integrations with Salesforce. With these new integrations, customers will be able to bi-directionally load data to Salesforce and Excel to build reports, visualize information and discover new insights. Salesforce integration with Power Query for Excel is in preview now, with general availability anticipated for the first half of 2015. Power BI integration with Salesforce is anticipated for the first half of 2015. A Salesforce app for Excel is anticipated for the second half of 2015. We are now into 2016 and no sign of this yet. I doubt we will ever see this now. I really really hate vapourware announcements at major tech conferences. |
From http://www.salesforce.com/company/microsoft-faq.jsp
...
- Open the Power Query Ribbon
- Click From Table
- First we want to get rid of the data we don't need - so all the tend and start columns
- Just go right ahead and delete the columns - don't be afraid, you won't damage anything!
- Right Click on the column heading > Remove.
- That looks better already
- Now see the side panel to see what it has done.
- It's a good idea to rename Removed Columns to something more descriptive.
- eg Removed date columns
- Now Change M and F to Male and Female
- Right click on the sex column
- Replace Values
- Value to Find = M
- Replace with = Male
- Match entire cell contents = true
- Oops, I made a mistake - I typed replace M with F. Ok that's easy.
- Just click the X next to Replaced Value in the panel and start again
- Oops, another mistake - I typed Femals rater than Female. Easy.
- Just click the cog next to the action in the side panel and make the adjustment
- Again rename your labels in the side panel
- Now, if you have more than a few values you won't use find ad and replace.
- Right click on the sex column
- Notice how some steps get combined (eg all the deletions) and some steps get added as each one (eg the replacements).
- At this stage look at the advanced editor so you can see what is happening
- Click Advanced Editor in the ribbon.
- You can edit this, but beware it does get confusing with a big data set.
- Now, let's format the dates
- The date format depends on which data loader you use to do the Import.
- We will use MM\DD\YYYY
- Right click on birthdate
- Choose Change Type > Date > By Locale
- Choose Date then English (United States)
- Do the same for Hiredate
- Remove Salary since Salesforce is not our payroll system
- OK, things are looking pretty good now, it may be time to remove the duplicates.
- You can remove the duplicates based on one or more columns https://support.office.com/en-nz/article/Remove-duplicates-Power-Query-d9cffc69-dc5d-4d94-8b66-72779688874d?ui=en-US&rs=en-NZ&ad=NZ
- In our case we will right click on EmpNo. and choose remove Duplicates.
- At this stage we will close and load and see what we've got.
- Oops! We have lost our Title and Department data.
- Right click on Table 1 in the side panel and click Edit
- Remove the Removed Duplicates action
- Phew! no data lost!
- Now highlight empno deptno and title and choose remove duplicates.
- Easy! Our data now looks like this
...