Power BI can not only import data from a variety of sources--it can even help with cleaning and formatting the data it pulls. But how easy is it to do this? With Power BI, it’s very simple to centralize multiple data sources into one report and create relationships between datasets to make insights easier to find. Today more than 59 different cloud services like GitHub and Marketo have specific connectors so it’s easy to import into Power BI from existing tools. Check out the steps below.
Editing and Importing Your Data
When you open Power BI Desktop, on the left side you’ll see an option to “Get Data” - start here, and a collection of possible sources will pop up. When you connect to a source, you may be asked to locate the source on a network or your computer or sign in for authentication.
The next screen you’ll see if the Navigator, which shows a snapshot of the data available from the source you chose. You can go ahead and click Load to import it as-is, or you can choose Edit if you need to customize or clean your data, or filter out subsets. Connecting to a folder allows you to import data from more than one source at a time.
If you need to edit your data, you’ll be taken to the Query Editor. You can also launch the Query Editor directly using the Edit Queries button in the Home ribbon.
The Query Editor lets you manipulate your data in several ways using the ribbon above or by right-clicking directly on the data shown in the center pane. To the left of the center pane you’ll find a list of the queries, or subsets of data, you’ve isolated. To the right of the center pane you’ll see Query Properties and Applied Steps within the collapsible Query Settings pane.
Applied Steps shows a log of every action you’ve taken with the data in your current selected query, so you can review and undo changes, or even just label the changes you’ve made so it’s easy for others to understand your process. This pane is very valuable for experimenting with manipulating your data in various ways--if you don’t like the results of a change you’ve made, you can easily undo it rather than starting over from the beginning.
Use text filters to sort your data by selecting the drop-down arrow next to a column. You can merge data from multiple sources into one table using the Append Queries tool--Power BI will attempt to match up the columns but you can make adjustments yourself, as well. Save your changes by selecting Close & Apply on the Home ribbon.
Cleaning and Optimizing Your Data for Visuals
Once you’ve finished using the Query Editor and loading your data into Power BI Desktop, you can choose from one of three views to look at your data--look for the icons on the upper left side of the Power BI canvas to toggle between the Report view, the Data view and the Relationships view.
Power BI’s visualization and modeling tools work best with columnar data--if your data isn’t in this format, Power BI can help you change it to suit. Two common ways of doing this are to transpose your data and format it.
To transpose your data, in the Query Editor you can turn columns into rows and vice versa. This allows you to break your data down to make it easier for Power BI to use.
Formatting your data makes it easier for Power BI to categorize and identify it when it’s imported. The FIll command will turn null values into the values found above or below, plus you can unpivot columns and more. It’s best to experiment with the changes to see which formatting moves work best for Power BI and that particular dataset. Remember, if a change doesn’t work like you wanted, you can undo it in the Applied Steps section.
Now that your data is loaded, you can start creating visuals and looking for insights! Stay tuned for more on how to do that next week and, as always, please get in touch with us for all of your Power BI, data cleansing and data integration needs.
