Wednesday, March 4, 2015

Power Query Tips

Some tips for making the most of Power Query, based on recent experiences


  • Natural order for processing data: 
    • Expand, 
    • Replace, 
    • Type, 
    • Remove Duplicates, 
    • Extrapolate, 
    • Order
  • Calculate/map/merge everything in PowerQuery (it's easier than PowerPivot)
  • Use copy & replace (with 0|1) & multiply, in place of boolean logic
  • Use multi-column joins when merging
  • Use code for loading folder of excel files - but be careful since some approaches are much more efficient than others

Note: PQ does not yet cache in Data Model, and even caching in the Workbook is flaky.

Tuesday, January 27, 2015

Power Query - loading multiple files

Issues with loading a directory of Excel files?

Try this (after you start the wizard)

let
    Source = Folder.Files("C:\Files"),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Content"}),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "GetExcelData", each Excel.Workbook([Content])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Content"}),
    #"Expand GetExcelData" = Table.ExpandTableColumn(#"Removed Columns", "GetExcelData", {"Name", "Data", "Item", "Kind"}, {"Name", "Data", "Item", "Kind"}),
    #"Added Custom1" = Table.AddColumn(#"Expand GetExcelData", "NoHeaders", each Table.PromoteHeaders([Data])),
    #"Removed Other Columns1" = Table.SelectColumns(#"Added Custom1",{"NoHeaders"}),
in
    #"Removed Other Columns1"

And then keep going as normal!

Some more explanation here:
http://www.poweredsolutions.co/2014/11/21/combining-data-from-multiple-excel-workbooks-with-power-querythe-easycompletepower-bi-ready-way-2/
and here:
https://cwebbbi.wordpress.com/2014/11/20/combining-data-from-multiple-excel-workbooks-with-power-querythe-easy-way/


Wednesday, December 3, 2014

What economics tells us about the trustworthiness of movie reviews

http://theweek.com/article/index/272531/what-economics-tells-us-about-the-trustworthiness-of-movie-reviews


Before you read that review of Interstellar, study up on your game theory

Imagine you're a film critic. Let's say you see a movie and, despite your vast reservoir of professional expertise, you're just not sure whether you should give it a positive or negative review. What should you do?
If your primary motive is to ensure that your reputation for good taste remains intact, you might be inclined to author a negative review. After all, if people read your review and then don't go see the film because you killed it, they'll never know you got it "wrong," right?
This strategy can backfire. Vincent Canby, who reviewed over 1,000 movies for The New York Times, panned some amazing films, including Once Upon A Time in AmericaChinatownOne Flew Over the Cuckoo's Nest, and Rocky. Plenty of people saw them and knew Canby got it wrong.
Maybe that's why studio hype is the real bane of a film critic's existence. Lots of things unrelated to a film's quality can affect a review, and even the expectation that the film might be good or bad skews opinion. Anthony Lane, probably the best living movie critic, has a rule: "Never read the publicity material." If a critic hates a film that he thinks everyone else might love (say, Inception), he might sensibly wonder whether he missed something and still write a loving review. Critics may be radicals in appearance, but they are conservatives at heart.
Now, you might argue that the logic train I've outlined above is far too cold and calculating for a high-minded critic judging something on its artistic merits. But movie reviewers are certainly influenced, if only subconsciously, by their sense of what the audience expects, and how their review interacts with those expectations. And economic theory can offer us some real insights into just how this works.
In a clever recent paper, Fanny Camara and Nicholas Dupuis, two Ph.D. students at the Toulouse School of Economics, used data from Rotten Tomatoes to study reviews of American films. The magic of their approach, called structural estimation, is that they managed to figure out how reviewers would judge a film without having to know how good the films actually are. They only needed to guesstimate how good the reviewers might expect the film to be, by looking at characteristics such as the director's track record, the film's budget, and whether it's a sequel to a previous box office hit. With this information on likely perceived film quality, as well as the reputation of reviewers taken from Google search, the whiz kids used their model to estimate what they expected the distribution of film reviews to be if the critics were completely unbiased in their reports, reviewing the movies only on their merits with no regard for whether they might expect a film to be good. Then they compared their prediction to what they observed on the critics' actual reviews.
So what did they find? Critics pan around 10 percent of bad films that the young economists expected the critics to have actually liked. But reviewers also pan up to two-fifths of apparentlygood movies that the young economists expected the critics would have actually liked. Robert Denerstein, a freelance film critic, came out on top, having consistently told the truth about films he enjoyed. Reviews by Kyle Smith of the New York Post, by contrast, were found not to track well with how good a film actually is. The reason, these Ph.D. students argue, is that, at least in part, reviewers are influenced by their expectations of whether the audience expects to like a film or not, and sometimes cater reviews accordingly, even when they might truly believe otherwise.
In this way, movie reviews fall into an area of economics, called game theory, that studies strategic behavior. Viewers and reviewers engage in a very particular kind of exercise, called a cheap-talk game. In this game, a movie reviewer tries to convince a potential moviegoer how good a film is. Yet reviewers are biased: Canby, for example, was known to have a soft spot for Woody Allen. Can the reviewer still convey this opinion of the film to readers? The review is useless if the reader thinks the reviewer is too biased.
One famous result in cheap talk games says that even a reviewer with a very small bias can only convince the audience whether he thinks film is "good" or "bad." A verifiably biased reviewer can never entirely convey all aspects of her nuanced opinion about a film, and the economists used this insight to understand how critics skew their reviews. And because talk is cheap when it comes to movie reviews, a simple thumbs up or down may tell you all you need to know.

Monday, November 3, 2014

How Mobile Payments (Google Wallet and Apple Pay) work

How Apple Pay and Google Wallet actually work

Many companies are part of your credit card transactions; few know what they do.

It's hard to have a meaningful discussion about Apple Pay (iOS' most recent foray into mobile payments) and Google Wallet (Android's three-year-old platform that's had tepid success) without talking about how the systems actually work. And to talk about how those systems work, we have to know how credit card charges work...