Ever wonder what Power Query can do for you? Here’s a quick demonstration of using it to load data from the City of Ottawa’s Open Data repository. Also a quick demonstration how Power Query and Power Pivot can be used to easily gain insight into the data.
One of the issues that is solved in this demo is that of inconsistent vocabulary. The data refers to the city wards differently at different times. The technique demonstrated involves making a conversion table to normalize the ward vocabulary. Then Power Pivot can make the connection using a relationship or Power Query can merge the normalized vocabulary into the main file.
Another issue being demonstrated is loading and aggregating the data directly from the Web. You can then stay abreast of city updates using a refresh function.