Friday, 23 May 2014

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 Smile

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
 
image

4. Double Click and I get the Data
 
image
 
I can manage the Column as well to adjust my CRM Fields
 
image
 
image
 
5. Then I do the same thing for my next requirement.
 
image
 
Let’s say you have multiple data source, then you can use Merge as well
 
image


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.

image

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 :

image


image


From OData
To get the Product Sample

image


image


From Database

image
 
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 Smile It gives me a very good shortcut.

Hope it helps!

2 comments:

  1. I have been surfing on-line greater than 3 hours lately, but I by no means discovered any attention-grabbing article like yours.

    It is pretty price enough for me. Personally, if all webmasters and bloggers made good content material
    as you probably did, the web shall be much more useful than ever
    before.

    Look into my weblog :: how to open csv how to open xml file In excel 2010

    ReplyDelete
  2. Hi, we have SharePoint 2013 and CRM 2015 on premise. But we still have Microsoft Office 2010. I am curious to know whether there are any issues, bugs, or difficulties when we use Excel 2010 with CRM 2015? If so, what are they? How about Excel 2013? What new benefits are there using Excel 2013 with CRM 2015 and SharePoint 2013?
    Thanks
    Allysa.

    ReplyDelete

My Name is..