Last week I decided to try an interesting experiment. Lets see if I can get data from the Twitter REST API into Power Pivot using Power Query. Power Query already supports two out of three things that are needed for me to import this data:
- Import from a web url is already supported
- Parsing JSON data is already supported
- Logging into the service with OAuth is not supported for a datasource that is not developed by the Power Query team. Unfortunately even though most products use OAuth, no OAuth implementation is the same. Thanks to some hacking with Power Query I managed to work around this.
So lets go take a look at how we can solve this.
Read more »
I was playing around today with some TFS data (that’s were we store all our product data ) and of course I was looking for a way to get this data into Power Pivot and Excel. I found a way
It turns out TFS online supports OData for most of their services. See this blog post on how to enable it and where to connect to: https://tfsodata.visualstudio.com/
The important thing is this:
Team Foundation Service authentication:
(Optional) In order to authenticate with Team Foundation Service, you will need to enable and configure basic auth credentials on tfs.visualstudio.com:
- Navigate to the account that you want to use on https://tfs.visualstudio.com. For example, you may have https://account.visualstudio.com.
- In the top-right corner, click on your account name and then select My Profile
- Select the Credentials tab
- Click the ‘Enable alternate credentials and set password‘ link
- Enter a password. It is suggested that you choose a unique password here (not associated with any other accounts)
- Click Save Changes
To authenticate against the OData service, you need to send your basic auth credentials in the following domain\username and password format:
- Note: account is from account.visualstudio.com, username is from the Credentials tab under My Profile, and password is the password that you just created.
So I did that for my TFS online instance and opened up PQ and selected import from OData:
Next I pasted in my workitems OData url (that I got from the site above):
This prompts me for authentication, and I choose basic auth and enter my credentials:
This opens up the query editor and I am good to go from that point:
It’s pretty cool that this worked and I though I share it
I just found out about this great new Azure service that allows you to run any app on any device, including several default templates like Excel 2013 Pro plus. So my first thought was of course, cool lets try to run Excel on the IPad we have at home.
First what is Azure Azure RemoteApp, from the website:
Azure RemoteApp helps employees stay productive anywhere, and on a variety of devices – Windows, Mac OS X, iOS, or Android. Your company’s applications run on Windows Server in the Azure cloud, where they’re easier to scale and update. Users can access their applications remotely from their Internet-connected laptop, tablet, or phone. While appearing to run on the users’ local device, the applications are centralized on Azure’s protected, reliable platform.
Azure RemoteApp combines Windows application experiences with the powerful capabilities of Remote Desktop Services on Microsoft Azure – the cloud for modern business.
Read more »
I had an interesting question on one of my older blog posts Filtering results to only show top x results in Power View chart last week:
Your logic above makes perfect sense, but I can’t crack it for some reason. This is making me Sad!!!! There is obviously something missing in my logic. I have created a measure as follows
The measure is called Ranking. However when I put this on my powerview chart the only value is 1. I want to sum the data by city and rank by the cities.
This is my data
Country City Sales
Germany Munich 1
Germany T 3
Germany D 2
Germany Munich 7
Germany A 8
Germany Munich 6
Germany P 9
This is actually a topic that I spend quite some time in one of my recent DAX session that I did at the PASS BA conference and TechEd. You can watch the session online right here: Solving Complex Business Problems with DAX
Read more »
A quick post today that came from one of the readers on the ask a question page:
Let’s say we have a dates table and fact table linked by a field called DatesID. The dates table has one field called DatesID and the fact table has two fields (DatesID and Quantity) as follows:
We have a measure for CurrentQuantity:=SUM(fact[Quantity]). When we select 5/3/14 on a slicer for dates[DateID], we get 3 from Pivot1. Everything is fine at this point.
Where I run into trouble is with Pivot2 where I want to list all dates within the last 30 days of the slicer date along with the corresponding quantity.
So for example: By selecting 5/3/14 on the date slicer, we want to produce the following on Pivot2:
Similarly, when we select 5/2/14 on the dates slicer, we would get 2 from our CurrentQuantity measure and 5/3/14 would drop from pivot2.
Read more »
I have had some questions about the example dataset that I used in the book and if this was publicly available. For the book I took the MecDatamarket database from the MS BI VPC and modified the data and loaded it into Access. You can download the file here: http://1drv.ms/1miiVPG
All rights of this dataset are reserved to Microsoft.
I got an interesting question last night in preparation for the world cup. How can I get a PivotTable where I can see the results where the team selected in a slicer is either the Home or the Away team. Because we need to do show games where the Home or the Away team are selected here we cannot use traditional relationships.
This is the table of games I have (yes its fake, I made it myself before you wonder if these games are actually played):
Read more »
Last week I was at the PassBA conference in San Jose and it was great meeting a lot of you. This week I will be at TechEd in Houston where I hope to chat with even more of you. You can find me at the following places:
- Monday night reception at the BI booth at the expo
- Tuesday at 12:15 to 2:30 at the BI booth at the expo
- Tuesday 5pm at Power hour
- Tuesday night, ask the experts
- Wednesdays 1:30 my deep dive into DAX session
- Thursday 8:30 my session on DAX over Multidimensional
- Thursday 10:45 at the BI booth at the expo.
TechEd will also be the first place where you can get a copy of the book, very exciting (for me)