Using Excel Power Query Online Search to Get Tons of CRM Data

I was attending a SQL Server 2014 Launching in Malaysia. Most of them were talking about PowerBI and SQL on the Cloud. Then, one of the best feature I can realize is the capability of Power Query to Query using external data source from Online Search.

It means that Power Query has capability to extract Public Data into directly to the Worksheet Table, without letting the user open the browser, then getting the data, copy one by one to the Excel table records. Wikipedia is one of the greatest source, they provide us the raw data.

Then, I was thinking to utilize this capability to get many data to our CRM demo purpose or Live purpose. When I had a presales activity and demo, what I did at the time is getting the raw data from Google then transform it into the Excel Table. Well, it is not efficient, since the data format from that Web is not in Excel or .CSV, so that I had to copy the data one by one to build a Excel Dataset to be converted as .csv file to be imported to CRM Database. Preparing data for demo sometimes is requiring more effort than developing it

Okay, then luckily, I found this method and I will explain to you.

So, my requirements are:
1. Get the Country List around the world, together with the ISO Code as Country Code
2. Get the State or Province List of Indonesia

What you and I need are:Excel 2013 or older version with Power Query.

You can download the Power Query add-on from this URL :
http://www.microsoft.com/en-my/download/details.aspx?id=39379

And here is the prerequisite for Power Query

System Requirements


Supported Operating Systems:
Windows 7, Windows 8, Windows Server 2008 R2, Windows Server 2012, Windows Vista

• Windows Vista (requires .NET 3.5 SP1)
• Windows Server 2008 (requires .NET 3.5 SP1)
• Windows 7
• Windows 8
• Windows 8.1

The following Office versions are supported:
• Microsoft Office 2010 Professional Plus with Software Assurance
• Microsoft Office 2013 Professional Plus, Office 365 ProPlus or Excel 2013 Standalone

Microsoft Power Query for Excel requires Internet Explorer 9 or greater.
Microsoft Power Query for Excel is available for 32-bit (x86) and 64-bit (x64) platforms, your selection must match architecture of the installed version of Office

Then here is the link about Power Query :

http://office.microsoft.com/en-001/excel-help/introduction-to-microsoft-power-query-for-excel-HA104003940.aspx


Then, here is the Magic.

1. First, go to Excel
2. Go to Power Query and Search Online
3. Type what I want to search and get the result
 


4. Double Click and I get the Data
 

 
I can manage the Column as well to adjust my CRM Fields
 

 

 
5. Then I do the same thing for my next requirement.
 

 
Let’s say you have multiple data source, then you can use Merge as well
 



If I don’t use this feature, I have to go to the Public Data, for example Wikipedia to get the data and then copy paste to our Excel file.



And beside that, I also can go to specific web URL and specific Database as source as well, based on the Authentication, of course, since some of them are not public data.

From Web :







From OData
To get the Product Sample







From Database


 
By using this guy’s help, I can generate many data to my CRM without compiling, copying, and pasting the data from Public Data to Excel It gives me a very good shortcut.

Hope it helps!