PowerBI.tips

Tag: Get Data

Power BI datasets: A Method to the Madness

As report authors we sometimes get caught up in how easy it is to create a report and provide value to the business. Each report is an opportunity to make a big contribution to the organization. Power BI makes it easier than ever to turn many of those reports around quickly. This is a good thing of course. But, sometimes we can get caught up in the madness of turning out another report with only a flash of recall that we could have used the same or similar model done in a different report. The internal monologue kicks in.

“Pffft! Re-use a model, that is way to much work! Why do that?, when we can just create a copy of the PBIX (Desktop file) and have two models to manage with the two reports for the same business area! That sounds like fun!”

Hmm… Or does this actually sound awfully similar to the challenges one might face with sprawling Excel solution. Where variants of logic are buried in different files. Only the composer knows how to bring order from the chaos. Might I suggest that we spare ourselves that sort of pain. Just learn how we can easily leverage our already hard fought model work. Avoid tedious updates without having to over complicate our nirvana of sticking to the business world, but how would we do that?

The Answer

Power BI datasets. Power BI datasets allow us to re-use our model across multiple reports. This simplifies and speeds up future report authoring. Also this gives us the building blocks for sharing that dataset to a wider audience.

How do we create one?

Technically, we’ve likely already created many. You see, when we publish a Power BI report, we publish a dataset along with it. This dataset is stored in the Power BI Service, and our deployed report relies on it now. But the connector “Power BI datasets” allows us to connect directly to any of these datasets that we have permission to edit. This means that we have the ability to extend a single model across multiple reports without the need of standing up a separate Analysis Services server anywhere. This is a big deal, this allows the everyday business user to leverage a reusable model. A single change or update to a calculation can update multiple reports at the same time. One measure or calculation addition can be done in one place instead of many.

All we need to do to create a dataset that we can connect to is publish a PBIX file that contains data. I’ve adopted a practice recently and rather than generating my first report and reusing that model, I now upload a PBIX file that ONLY contains the model and I name it something like “Sales-Model”. Now I have an object that I’ll know serves the purpose of just being a model instead of a report. This makes it easier from a trace-ability standpoint when looking at the related objects in the Service or selecting it from my list of options when choosing my dataset.

How do we use one?

Using the Power BI dataset is one of the most straightforward connections in Power BI. Selecting Get Data -> “Power BI datasets”

This brings up the menu of all the datasets in the Power BI Service. The list that is shown are the datasets that our user account has access to use. The great thing about these datasets are we now have the ability to connect to and use a dataset from a different workspace provided we have permissions to edit them. This feature is called a Shared Dataset. Select the dataset and your report is automatically connected to dataset.

Now, what we’ll notice here is that using this feature automatically pulls in a model for us and we can start building our report. This data source connection behaves exactly the same as if we created our own “live” connection to an Analysis Services instance we would set up. Probably not shocking to any reader here. But, that is exactly what is happening in this case as well. We get the benefit of Microsoft handling all that painful work for us while we reap the benefits of a streamlined process.

As with any “easy button” solution, there are pro’s and con’s. What I mean is that in our new reports we do have easy access to the model. Now you can start building reports immediately. We don’t have the ability to modify the model or the ETL processes. If we want to edit then we need to go back to the original dataset to make those changes.

But the minor inconvenience of having 2 PBIX files open if we need to in order to make updates to the model is trivial compared to being able to connect many reports to that single model. The live connection does still allow the report author the ability to create measures. So, if there are measures that are only suited to our report and not the overall model we still have the ability to add them.

Once we’ve completed our report, we just publish as we normally would, only this time the dataset is already out in the Service and only our report is published. There are so many things we can now do to share that dataset, but we’ll leave that to another article.

If you’ve never used this method before, I would highly encourage you to try it out. Any time you can save yourself now with reducing the number of models you maintain, the faster you can produce more reports. You now spend less time maintaining all the reports you are publishing.

Happy report building!

If you like the content from PowerBI.Tips, please follow us on all the social outlets to stay up to date on all the latest features and free tutorials. Subscribe to our YouTube Channel, and follow us on Twitter where we will post all the announcements for new tutorials and content. Alternatively, you can catch us on LinkedIn (Seth) LinkedIn (Mike) where we will post all the announcements for new tutorials and content.

As always, you’ll find the coolest PowerBI.tips SWAG in our store. Check out all the fun PowerBI.tips clothing and products:

October 2, 2019
Loading Excel Files from Sharepoint
This is a quick tutorial on how to load Excel files from a SharePoint page. SharePoint is a nice landing place for your data because it can be connected to the PowerBI.com service and thus can be used to schedule refreshes of data within your company (if you already have a SharePoint o365 account).

This tutorial will be a slightly different than my previous tutorials as I don’t have a publicly available SharePoint site that can be used to connect to. So you will have to slightly adapt what I’m presenting to you to fit your particular SharePoint needs.

First you must start off with a SharePoint with a document library that includes an Excel file.

Sharepoint Location

The document library is titled SampleDocs, and the file we want to bring into PowerBI is called SampleData.

Clicking on the Home in the left navigation will take you to the home location of the SharePoint site. Copy down the HTML site address from your browser of this location it should look similar to the following:
```
https://partner.onmicrosoft.com/sites/[Your Site Name]/SitePages/Home.aspx
```
Open up PowerBI Desktop and on the home ribbon click Get Data. Highlight the SharePoint Folder and click Connect to continue.

SharePoint Folder Connection

Upon clicking connect you will be presented with another screen asking for the SharePoint folder location. In the URL window you will add the SharePoint site that we identified above. However, it is important to note that you don’t need the entire web address. Rather PowerBI only needs the specific site name, thus all that needs to be inserted into the URL field is highlighted below in Red.
```
https://partner.onmicrosoft.com/sites/[Your Site Name]/SitePages/Home.aspx
```
The ending “Sitepages/Home.aspx” can be removed.

Enter Shortened Site URL

Clicking ok will present a authentication screen. Depending on your company or SharePoint authentication you will need to enter the credentials to log into the SharePoint Site. You may have to try a couple different connection methods until you are able to properly connect to the SharePoint site. In my example I had to select Organization Account then click the Sign in. I signed in with my credentials given me via my I.T. group. Also, I had to use the drop down to select the proper level to apply the settings. I used the same address as listed above: https://partner.onmicrosoft.com/sites/[Your Site Name]/

User Sign In Page

After signing in click Connect to proceed. PowerBI Desktop will then load all the files from the SharePoint site in a preview window. Click Edit to modify the query.

Query Editor View

We can now see our SampleData File and the folder path. Each document library will be a separate folder path, thus if you have multiple document libraries then you will have all the files in those different folder paths.

Next click the double down arrows to load the excel file.

Load File

Power BI Desktop will then go to the SharePoint site and download the information inside your excel file. For my data I have all the information retained in a table within my excel document. The table name is call MyDataTable. Thus, clicking on the Table link in the MyDataTable row I will be able to open all the data within this table.

Load Table of Data from Excel File

Finally the data is loaded from the excel table. Click Close & Apply on the Home ribbon to load the data into PowerBI.

Note: It is always important to check your columns and verify that your data types are correct. Highlight each column and make sure you select the proper Data Type for each column. Data Type can be found on the Home ribbon.

Final Load Data

Thanks for visiting. Make sure you stop by again for more great tutorials.
September 1, 2016
Load Multiple Excel (xls or xlsx) Files
Previously we’ve done a tutorial on loading multiple text files within one query. This is nice, however we will also need to import multiple Excel files. First, to understand the procedure of querying multiple excel files you have to understand the basics between the CSV (comma separated values) file and an excel (.xls or .xlsx) files. In a CSV file you have only one data set. The beginning of the file starts with values and separates each file with a “,” a carriage return starts a new row of data. This is an easy and efficient way to store millions of rows of data. By contrast the excel file is way more complicated. Excel files can have multiple sheets of tables of data. Think of this as a stack of CSV type files. For example if you have an excel workbook with three sheets of data, Sheet 1, Sheet 2, Sheet 3. You can think of those three sheets as grid of data, similar to the CSV file. The multiple sheet aspects of an excel file makes the data ingestion into PowerBI a little bit more complicated. To add to the complication, when you loading data from either multiple sheets, or selecting a specific out of many sheets of data. For illustration purposes imagine working with two excel files with three sheets each, 2 x 3 = 6, a total of 6 sheets of data, or what I will call “pages” of data. This is why it is more complex to load excel files than CSV files.

Note: If you want to learn how to load multiple CSV files visit this tutorial.

Not only do you have to figure out what data you want to ingest on the page you must all tell PowerBI which sheets do you want to look at, and from which excel file. If that was to many words think of loading the following data sample:

Workbook 1 – Year 2000 Olympic Medals
- Sheet 1
  - Olympic Medals Table
    
    Rank
    
    Country
    
    Gold
    
    Silver
    
    Bronze
    
    Total
- Sheet 2
- Sheet 3
WorkBook 2 – Year 2004 Olympic Medals
- Sheet 1
  - Olympic Medals Table
    
    Rank
    
    Country
    
    Gold
    
    Silver
    
    Bronze
    
    Total
- Sheet 2
- Sheet 3
The data structure for both workbook 1 and 2 are similar but the names of the files are different and there can be multiple pages.

To resolve this we will have to write a M language function that will load each file as a function. This will be done in later in the tutorial.

Here is the data source information for Olympic medals won by each country from 2000 to 2012, download here. Inside the Medal Count zip file are four xlsx files, extract them to your desktop. Move the files into a folder on your desktop labeled Medals.

Medals Folder

Now, open up PowerBI, We will begin shaping our data to load all the excel files. On the Home ribbon click on the Get Data button. Select Folder on the right side and click Connect.

Get Folder Data

Next select the folder path that you want acquire the files from, Click OK to continue.

Load Folder Screen

Next we are presented with the loaded files within our selected folder. Click Edit at the bottom of the screen to proceed. The Query Editor window will now open. Select the first two columns labeled Content, and Name. With those two columns selected right click on the header and select Remove Other Columns. This will remove all the useless data associated with the files.

Remove Other Columns

Click the Add Column ribbon and press the Add Custom Column on the left side of the ribbon.

Add Custom Column

Name the new column ExcelFileLoad and enter the following equation.

Excel.Workbook Equation

Note: Once you type “Excel.Workbook(” you can click on the column labeled Content on the right side of the screen to have the name automatically added. This is useful when you have many many columns to choose from or if there naming of those columns becomes complex. This way you won’t type in the column name incorrectly.

Click OK to proceed. Notice we now have a new column called ExcelFileLoad. Next click the Expand button (the one with the arrows) located at the right of our newly added column. Click OK to proceed.

Expand Column Button

Now we have a new column labeled ExcelFileLoad.Data, which is the data contained in our excel files. Now click in the Grey Area next to the word labeled Table. This will open up the file and reveal the information present in the file. Notice that we can see the headers and the data in our file. Row 1 contains the headers of each column. Rows after row 1 contains the medal data.

View Data of File

Next select the columns labeled Name and ExcelFileLoad.Data and right click on the column header, then select Remove Other Columns

Remove Other Columns Again

On the Add Column ribbon click Add Custom Column again. Name the column PromoteHeaders and enter the following formula. Click OK to proceed.

Promote Headers Step

Clicking again on the grey area in our newly created column reveals our tables with promoted headers.

View of Data with Promoted Headers

Next click the Expand Button, un-check the Use original column name as prefix and click the OK button to proceed.

Expand Data

Remove the following columns, ExcelFileLoad.Data, Rank, and Total, bu right selecting the columns and right clicking on the header and selecting Remove Columns. Now we want to parse out the year name from the Name column. To do this click on Name Column. Then click the Transform ribbon and click the Extract button, then select First Characters from the drop down menu.

Extract First Characters

In the Extract First Characters menu enter the number 4 and click OK to proceed.

Extract First 4 Characters

Change the following columns to whole numbers: Name, Gold, Silver, Bronze. Do this on the Transform ribbon in the Data Type drop down.

Change Data Types

We are now ready to load all the data. Rename the Query to Medals, click the Home ribbon and select Close & Apply.

Name Query

And there you have it. We have successfully loaded four excel files into one query.

Bonus: for added flare add the following measure.

Total Medal Count = sum(Medals[Gold]) + sum(Medals[Silver]) + sum(Medals[Bronze])

Now you can add the following Visualizations.

Bar Chart Visual

Stacked Bar Chart

Map Visual
August 10, 2016
Using Variables for File Locations
This week I encountered an issue when working with multiple queries in my data model. Here is the source files in case you want to follow along.

Here’s what happened. I had a PBIX file that had four queries in it, one file for the summer the Olympic metal count for the following years, 2000, 2004, 2008, and 2012.

Olympic Metal Count

After a bit of working I figured that my desktop screen was going to get to cluttered if I continued to collect Olympic metal data. Thus, I moved my excel files which were my source data into a folder called Olympic Medals.

File Move

By doing this I broke all the links for all four files. This was discovered when I tried to refresh my queries and noticed that all the queries failed. Power BI gave me a nice little message notifying me that there was a data source error.
```
DataSource.Error: Could not fine the file:
```
Missing File Error

To fix this I had to open the query editor and change each file’s location to the new folder that I just made. Seeing that this is not an efficient use of my time, I decided to spend more time to figure out a way to make a variable that would be my file location for all my queries.

Lets begin by making a new blank query by clicking on the bottom half of the New Source button on the Home ribbon. Then click the item labeled Blank Query.

Start Blank Query

With the new query open type in the file location where you will obtain all your working files. For me my file location was on my desktop, thus the file location is listed below. Rename the new query to Folder.

Folder Query

Note: Since we are working on building a file structure for Power BI to load the excel files you will want to be extra careful to add a “\” back slash at the end of the file location.

Next on the query for Medals 2000, we click the Source under the applied steps window on the right. This will expose the code in the formula bar at the top of the window.

Select the Source Applied Step

Note: If you don’t see the formula bar as I have illustrated in the image above, you can turn this feature on by click the View ribbon and checking the box next to the words Formula Bar. This will expose the formula bar so you can edit the source step.

This is where the magic happens. We can now insert our new blank query into this step. Our current file contents looks like the following:
```
= Excel.Workbook(  File.Contents(  "C:\Users\Mike\Desktop\Olympic Medals\2000 Medals.xlsx") ,  null ,  true )
```
Now remove the first part of the file location and make the equation match the following:
```
= Excel.Workbook(  File.Contents(   Folder  &   "2000 Medals.xlsx") ,   null , true )
```
Not only does this shorten our equation, it now uses the folder location we identified earlier and then we can pick up the file name 2000 Medals.xlsx. This makes is very easy to add additional queries with the same steps. Also, if you move your files to a new folder location, you only have to change the Folder query to reflect the new file location. To test this make a new folder on your desktop called New Folder. Move all the Olympic medal files to the new folder. Now in Power BI Desktop press the Refresh on the Home ribbon. This should result in the Data.Source.Error that we saw earlier. To fix this click the Edit Queries on the Home ribbon, select the Folder query and change the file directory to the new folder that you made on your desktop. It should look similar to the following:

New Folder Image

Once you’ve modified the Folder query, click Close & Apply on the Home ribbon and all your queries will now reload. Success!!

New Queries Loaded

Hope this tutorial helps and solves some of the problems when moving data files and storing information for Power BI desktop. Please Share if you like the tutorials. Thanks.
August 4, 2016
Query Editor – Editing M Code

In this tutorial we’ll learn how to copy and paste queries to and from the Query Editor. When your working in Power BI Desktop often you will need to share and model the data before it can be applied to the visual. In my experience you’ll need to add a calculated column or break out a date such as 1/5/2016 into the the Year 2016 or Month 01, components to properly display a visual.

We will start off with from a prior example where we build a shaded region map. The tutorial to create this Power BI Desktop file is located here.

If you want to cheat and download the final PBIX file you can download and open the zipped file here: Regional Filled Map Example

This file was made in Power BI Desktop April 2016 version, 2.34.4372.322, download the latest version from Microsoft Here.

Open the zip file that you downloaded and extract the file inside labeled Regional Filled Map Example. Open the file. Once you’ve opened the file on page 1 of the you see a map of the united states that looks similar to the following.

Opened File with Map

Now we well enter the query editor. Click on the Edit Queries on the Home ribbon. You opened the Query Editor. In this window we shape and model the data so we can properly visualize it on the pages. Couple of things to notice here. Every time you press a button on the ribbon, the query editor generates an Applied Step. Each step writes a line of M code which transforms the data as it is loaded into the computer’s memory. In this case we have (7) seven steps starting at Source and ending with Changed Type1.

Query Editor Revealing Applied Steps

We want to expose the code that is begin generated at every step behind the scenes. Click on the View ribbon and then click on the button called Advanced Editor.

Query Editor – Advanced Editor

Opening this window reveals the M language code that is generating each Applied Step we saw earlier.

Note: some of the steps we saw earlier such as Filtered Rows had a space in it. In the query editor any applied step had a space in the name gets the added #”” around the applied step name. Thus, in the query editor Filter Rows would be #”Filtered Rows”. The hashtag and the quotations define the complete variable. If you changed the name of the applied step to FilteredRows, with no space. In the Advanced Editor you’d only see the step labeled as FilterRows, no hastag or quotations needed.

Now that the M language is revealed you can made modifications to the code. In cases where you want to make a function you would do so in the Advanced Editor. For our example today select all the code and copy it to the clipboard using the keyboard shortcut CTRL+C. Click Done to close the window.

Now lets copy that code into a brand new query. Click the Home ribbon, then click New Source, scroll all the way to the bottom of the list and select Blank Query. Click Connect to start a blank query.

Get Data – Blank Query

A new Query will now open up. Click the View ribbon, then click Advanced Editor. A blank editor window will open.

Blank Query

Paste the code we copied earlier into this window. Now the new Query1 should look like the following:

Paste Code in to Advance Editor

Click Done and the new query will now load. It is that simple, now we have two identical queries.

May 19, 2016
Manually Enter Data
There are often times when you need a small data set in order to make a visual behave exactly how you want it to. This may mean you need a small table to represent a range of numbers or text values.

Here are the Resources for this tutorial:
- Power BI Desktop (I’m using the March 2016 version, 2.33.4337.281) download the latest version from Microsoft Here.
To enter your own data Click the Enter Data button on the Home ribbon.

Enter Data Button

Next you are prompted with the Create Table window. In this window you are given the layout of a unfilled table. To begin entering data you can click in the first cell in Column one and start entering data. By pressing enter a new cell will populate below. You can Rename the column by double clicking the column name. To add a second column you Click on the * symbol next to your existing column. Finally to edit the table name you can type in the desired table name in the Name input box in the bottom left hand portion of the window.

Create Table Window

Finally, you can either to choose to Load the data as is or Edit the data to make additional changes (this can be useful to edit the data types of each column or to populate equations in subsequent columns). For the sake of this tutorial we will simply load the data. Click Load to load the data into the data model.

Now drag over the columns into the page view to begin generating visuals. By default PowerBI makes a table of data to show you the values you just entered.

Visual of Sales Table

Select the table visual (you know it is highlighted when it has the trim boarder as shown above) and Click the Doughnut Visual. This transforms the data into a doughnut, and who doesn’t like a nice data doughnut? Click anywhere in the page to de-select the new doughnut visual. Add a second table by dragging over he Region and Sales columns. We can now see the pretty graphic and the numbers supporting that visual.

Visuals Made with our Custom Data

I bet you didn’t notice that something changed here. Look closely at the data we see now vs. what we entered earlier. Go ahead, scroll up, I’ll wait… Did you catch it?

We now have 5 rows of data but we entered 6 before. That is because the Sales column is a number column and can be aggregated. Look in the fields column and you see there is a little sum symbol in front of the Sales column. This means that this column has a default summarization associated. To see what is the default summarization highlight Sales by clicking on the column name in the grey area. Then Click the ribbon titled Modeling, and there it is in the properties section the Default Summarization is Sum. Every time you use the Sales column it will be summarized in the tables and visuals views. Our visual table shows Brazil with a total sales of 600, because we had two Regions labeled as Brazil 500 and 100.

Now you can click on any of the data points in the doughnut. Notice the table automatically filters down to only show the areas you selected.

Data Filtered to only Brazil

ProTip: you can select multiple selections by holding down CTRL and selecting multiple items in the visual. You can only do this inside of one visual. As soon as you click another visual all filtering will disappear.

Again, I hope you enjoyed this quick tutorial. If you liked it make sure you share it below.
April 13, 2016
Query Settings – Fixing a Missing File
One of the most important concepts to learn within Power BI Desktop is how to build a Data Model.

Note: In simple terms the Data Model is data that is collected from the get data function. In your data model you can build multiple queries. This data is stored in the file. The data storage is very efficient as the data compressed down to approximately a 4:1 ratio. 1000 KB file will compact down to approximately 250 KB when loaded into Power BI. From my current understanding all data is loaded into the memory of the computer. Thus, if you are having performance issues it could be in part due to the RAM of your computer.

As you begin to craft more data models you will learn little tips and tricks along the way to make an efficient Data Model for your visualizations. I have found that the most challenging part of building the data model is structuring the data in a way that will make your selected visual make sense. This may mean you need to add a measure or a calculated column or a ranking to a data set. Alright lets get started.

Here are the Resources for this tutorial:
- Power BI Desktop (I’m using the March 2016 version, 2.33.4337.281) download the latest version from Microsoft Here.
- We are going to work through the Power BI Desktop file that we built in the Loading Excel Files Into Power BI Tutorial. You can follow the link to create the Power BI Desktop (pbix) file in the tutorial. For convenience, the completed file can be downloaded here: Import Excel Tutorial.
I’m going to start off by extracting the Import Excel Tutorial.zip file to my desktop. Once the file has been extracted we can open the containing folder. In this folder there are two files the source data in the excel file and the Power BI Desktop file.

Note: A Power BI Desktop file has a .pbix file format ending.

Open the Import Excel.pbix file. First click the Home ribbon and then click the Refresh button. Most likely there will be an error similar to the following message.

Message box when file can’t be found

This type of message occurs when you refresh a query and the file is missing or can’t be found. This is because when i originally built the Power BI Desktop tutorial the excel file that is supply the information was located on my desktop. This is a common problem when you build connections to local files stored on your computer. If you move a file into a different folder then the connection will break.

To resolve this close the message window by clicking Close. on the Home ribbon click the Edit Queries button. The Query Editor window will be presented. In a large yellow bar in the data view portion of the window (circled in red) is the error message.

Note: Circled in blue is the Query Settings window. This window is the window for all the applied steps to transform the data. You can change the name of the query in the name box. From the view we have selected we can see that the step entitled Changed Type is currently selected (seen circled in blue).

Click the grey button labeled Go To Error which is found in the yellow error box.

Error seen inside Query Editor

Upon clicking the Go To Error button the selection in the Query Settings button to the Source Step. This is where the query has failed. More information about the failure is shown in another yellow error box. This time click Edit Settings in the error box.

Edit Settings in Error window

Now we have the Load Excel file window prompt open. In this window Click Browse, navigate to where you extracted all the files downloaded earlier in the tutorial and select the excel document entitled Book1. Click Open and the new file location will be loaded into the Load Excl Window. Click OK to complete the settings change.

New File Location

Now the data is correctly loaded into the data model. Notice we are still on the step called Source. Take some time to click through each step, Source – Navigation – Promoted Headers – Changed Type. As you click on each step you can see how the data is transforming.

To see the code that is being used to make each step click the View ribbon and check the little box entitled Formula Bar. This will make a formula bar appear. When you click on a step the formula bar will reveal the code needed to complete the selected step.

Toggling on the Formula Bar

We can now see the equation, which is similar to how you would write an equation in excel. The code in the Changed Type step is here:
```
= Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"Sales", Int64.Type}, {"Category", type text}})
```
The equation is using the M language to transform the data. More information on the usage of the M language can be found here.

Note: Couple of pointers about the data shown in the formula. The function is called Table.TranformColumnTypes. The source of the data is a variable called #”Promoted Headers”. The pound sign and the words following in quotations is how the M language passes variables that have a space contained in the language. Since the prior step has the name “Promoted (space) Headers” the program has to add the pound sign and the quotation marks. If there is no space in the naming convention such as “PromotedHeaders” then only the PromotedHeaders would be seen in the code and the pound sign and quotes will be gone. See modify coded when I remove the space from the Promoted Headers applied step.
```
= Table.TransformColumnTypes(PromotedHeaders,{{"ID", Int64.Type}, {"Sales", Int64.Type}, {"Category", type text}})
```
Notice the the pound sign and quotations are missing.

The second part of the formula is an array which has been written out in curly brackets:
```
{
{"ID", Int64.Type}, 
{"Sales", Int64.Type}, 
{"Category", type text}
}
```
I changed the code by adding line returns to make it easier to read. The coded array has beginning bracket and an ending bracket. Each parameter is contained in it’s own curly brackets and separated with a comma. The array is a 2 x 3 array, it has 3 rows and two data points on each row, just like a matrix. The first data point is the column name. In the first row the column that is being address is called ID. The data transformation parameter is called Int64.Type. This means that the data is an integer type 64 bit. This repeats for each row until all parameters have been addressed.

So there you go, we have opened up a query repaired it and learned a little about the formula bar.

As a side note, as you build queries each button press that you make on the various ribbons in the Query Editor will make a minimum of one step the in the Query Editor.

Hope you enjoyed this short tutorial about the Query Editor. Make sure you share below if you liked it.
April 11, 2016
Import an Excel file into PowerBI

We are going to kick this blog off with a simple example of how to load data from excel into Power BI Desktop.

Note: I’m a firm believer of always understanding your data. If you are receiving data files or extracts from an automated system or from an individual, trust me it will make a difference. So, make sure you understand the source of the data and how the structure of your data may change over time. For example, you have have a column that has both text values and number values; or the data may add additional columns in the future. Thus, the data load into Power BI Desktop (PBID) will need to be flexible.

Lets start off with some simple data in excel:

Sample of Data in Excel

We have three columns of data, two have number in it and one has text values.

For now we will close out of excel and jump over to Power BI Desktop. Once the program loads we will click the Home ribbon then select the Get Data button.

Button for Get Data

After pressing the button a new menu will pop up showing us all the sources where data can be ingested from. The very first item in the list is Excel. Click the Excel then click the Connect button in the lower right hand corner.

Select Excel as Data Source

After clicking Connect a new window will pop up asking for the location of the Excel file. Navigate to our sample data called Book1.xlsx you can down load the actual file I used here: Book1 I saved my Book1.xlsx file on the desktop of my computer. Select Book1 and then Click Open.

Open Excel File Dialog Box

Next we are presented with the Navigator screen that reveals what is inside the workbook. There are two sheets. For now we are only interested in the data on Sheet1. Select Sheet1 and then click Load. This will load our data from Sheet1 into the Power BI Desktop data model.

Navigator Selection Screen

Now our data has been added to the Power BI Desktop data model. The data and the various columns we loaded can be found in the tool bar at the far right of PBI called Fields.

Location of Loaded Excel Data

Tech Tip: Power BI Desktop (PBI) opening the file and loading the relevant data into the memory of the computer. This has an approximate 4 to 1 compression ratio. In practical terms this means that a 100MB file will only consume 25MB of file size in PBI when it is saved. This is extremely useful as the data model can be quite large when loading multiple data files but the PBI file will compress down to a manageable size.

Make a Data from Column Sales and Category

Finally, the Sheet1 data table can be expanded into is respective columns by clicking the triangle next to the table icon. Finally you can drag and drop the column names into the visualization page to begin making visualizations. For this demo I used the Category Column and the Sales column to make a table.

By selecting a different visualization in the visualizations bar you can change your data table into a Bar Chart.

Data Transformed into a Bar Chart

Well that is it for the first tutorial. Share your thoughts and comments below. Let me know if you have any suggestions on what you would like to see next.

March 29, 2016