PowerBI.tips

Tag: Query Editor

  • Adding Data Types Within Query Editor

    Adding Data Types Within Query Editor

    If you have spent any time working in Power BI, your very first step is to, wait for it… Get Data.  Using Get Data will start loading your data into the Query Editor for Extracting, Transforming and Loading (ETL).  When you start out in Power BI it is likely that you don’t spend much time in the Query Editor.  However, the longer you use Power BI desktop, and the more you learn, you find that the Query Editor is highly under-rated.  There are so many amazing transformations that you can perform on your data.  After some time getting comfortable you’ll be building larger queries with many, many, steps.  Eventually, it may look something like this:

    Multiple Query Transformations
    Multiple Query Transformations

    Perhaps your queries are already long, or may be even longer.  Wouldn’t it be nice to shorten the number of steps?  It would make it easier to read.  In this tutorial we are going to talk through how we can combine several steps when you create a new column.  This is achieved by modifying the M scripts or equations slightly when new columns are created.

    While doing this won’t cut down every query in half, but it will remove a couple of additional steps per query.  This makes your queries easier to read and maintain.  Also, using this best practice, will save you headaches in the future.  At some point you will run into a data type error.  This is seen when you try to join multiple tables on columns with different data types, or when you need a measure to create a SUM but the column data type is still text.

    Let’s get to the tutorial.

    Open up your Power BI Desktop program and on the Home ribbon click Enter Data.  Using the dialog box for entering data enter the following table of data:

    Sales
    100
    120
    94
    20
    80

    Once you’ve entered your data the Create Table screen should look like the following.  Be sure to name your table, in this case I named my data table Data…. yea, feeling a lack of creativity today.  Next, click Edit to modify the query before loading the data into Power BI.

     

    Create Table
    Create Table

    This will open the query editor.  Click on the Add Column ribbon, then select Custom Column.  The Custom Column dialog box will open.

    Note: When you bring in the created table that the Sales column already has the data transformed into a whole number. Also note in the right under Applied steps we have two steps, one for the source and one for Changed Type.  This is because not every M equation (M language is the language used to perform the ETL in the query editor) can handle data types.  

    Add Custom Column
    Add Custom Column

    In the Custom Column dialog box enter the following, the column name, the equation below.  Click OK to add the column.

    Insert Custom Column
    Insert Custom Column

    Note: It is good practice to name the new column something meaningful.  This helps when you are working in the query editor.  When you come back to your query months later and wondered what you were creating, the column names will help!  Trust me I learned this lesson the hard way… 

    Great, now we have our new column.  Notice the image in front of our column named Increase Sales.  This means Power BI thinks that the data type of this column could be Text or a Number.  Let’s change it.  Click on the ABC123 icon and select Whole Number.  Now the column data type has changed to numbers only.

    Change Column Type to Whole Number
    Change Column Type to Whole Number

    If we glance at the Query Setting under the Applied Steps, we now have 4 steps.  Two were added, one for the added column and the second for the data type of the column.  This is not what we want.  Instead we would like the column to be added with the appropriate data type right from the beginning.

    Let’s remove the very last step labeled Changed Type1.  To do this we will click on the little X next to the step.  This will remove the step.  While highlighting the Added Custom step click in the formula bar and modify the equation to include the following statement in RED.  Press the Enter to execute the new formula.

    = Table.AddColumn(#"Changed Type", "Increase Sales", each [Sales] * 1.1, Int64.Type)

    Note: if you don’t see the formula bar it can be toggled on or off in the View ribbon in the check box titled Formula Bar.

    The query editor should now look like the following:

    Desired Data Type
    Desired Data Type

    Without adding an extra step on the Query Settings, we have changed the data type.  I know this might seem trivial, but when you are creating large queries, they can get difficult to read.  For me, I find this technique quite useful, and it doesn’t only support whole numbers.  This technique also supports the following data types:

    Data Type Syntax
    Whole Number Int64.Type
    Decimal Number Number.Type
    Dates Date.Type
    Text Text.Type

    Thanks for following along.  If you liked this tutorial, please share it with someone else who might find this valuable.  Be sure to follow me in LinkedIn an Twitter for posts about new tutorials and great content from PowerBI.Tips

    Linkedin Twitter
  • Using Parameters to Enable Sharing

    Using Parameters to Enable Sharing

    This week I had a number of team members tell me how difficult it was to share a PBIX file and the corresponding data between team members.  The department hasn’t committed 100% to the idea of using PowerBI.com, and thus wanted to share the actual report.  I pointed my team mates to my earlier tutorial about using variables for file locations.  After reading the tutorial the team came back with a bunch of griping about how they didn’t want to go into the query editor, people name variables different things, and thus it’s hard to understand what another team member was doing, blah, blah, blah…

    So, in order to make peace I took another look at trying to make the sharing of a PBIX file easier.  This time I decided to use the Parameters feature within PowerBI.  This feature within the Query Editor was released all the way back in April of 2016.  I read about it, thought it was interesting but couldn’t find a really good use case.  In my current predicament, the parameters feature is the perfect solution.

    By using a parameter to store a folder and or file location you enhance the user experience by making it easier for a user to change the Query.

    To modify a parameter after it has already been built click on the bottom half  of the Edit Queries button, found on the Home ribbon.  A menu will pop up and you can select Edit Parameters.

    Edit Queries
    Edit Queries

    The Enter Parameters screen will pop up.  This is where you would change the parameters for a query.  In our example this is where we will enter a new folder location for our data.

    Enter Parameters Screen
    Enter Parameters Screen

    Let’s begin the tutorial.  To start we will need to work with some data.  In my earlier tutorial, I used the Olympic Medals Count.  We will reuse this dataset.  Start by downloading the zipped folder Olympic Metal Count files from the following location.  Once you have downloaded the sample data, extract the folder and subsequent files from the zipped file.  In windows 10, which I am using, you can right click on the downloaded file and select Extract All..

    Extract All from Zipped Folder
    Extract All from Zipped Folder

    A pop-up window will appear to complete the extraction process. By leaving the default settings the folder will be extracted into the same location as the downloaded file. Click Extract to complete the extraction.

    Complete Extraction
    Complete Extraction

    We now have a folder with all our data.

    Extracted Folder
    Extracted Folder

    With the data prepared we can now make the parameters within PowerBI.  Open PowerBI and click the top half of the Edit Queries button.  The Query Editor will open.  Click the Mange Parameters found on the Home ribbon.  The Parameters screen will open.  Click the blue New button at the top left.  Enter the name Folder, and include a brief description of this parameter.  Since the folder parameter is essential to the query we must leave the Required box checked.  Change the Type to Text, and leave the Suggested Values to Any value.  Finally enter the Folder Location where our Medal-Count file is located into the Current Value.  Click OK to complete the parameter.

    Make Parameter
    Make Parameter

    Next, we will create the actual query to load the folder containing our Metal Counts.  Click on the New Source button.  This will open the Get Data window.  Select the Folder option located in the list on the right.  Click Connect to complete the Get Data.

    Connect to Folder
    Connect to Folder

    The Get Data window will close, then a new window asking for the folder location will open.  This is where the parameter implementation is slick.  Click the ABC button to the far right of the Folder Path bar.  Change the selection to Parameter and select our newly created parameter labeled Folder.  Click OK to load the Folder.

    Change to Parameter
    Change to Parameter

     

    Select Folder
    Select Folder

    Next the folder prompt opens allowing us to accept or edit the query.  Click Combine & Edit to proceed.  The Combine Files window will open.  Here we can interrogate each file to inspect what is in each file.  Clicking the drop-down menu next to Example File: allows us to see the list of all files.  Select the 2004 Medals.xlsx file.

    Select Drop Down
    Select Drop Down

     

    Click the Sheet1 item on the left side of the window.  This exposes the data that is contained within this 2004 particular file.  Click OK to proceed.

    Select Sheet1
    Select Sheet1

    Note: For users that have been working with PowerBI for a while, this Combine Files screen flow is a newer feature.  I feel that this greatly simplifies the process and makes it easier to detect issues when loading data. 

    PowerBI now loads the files into the Query Editor.  The Query that we want to use is labeled Query2, change the Query2 name to Metal Count.

    Metal Count
    Metal Count

    What you’ll also notice is there is a lot more queries on the left side of the screen under Queries.  These queries were auto generated when we navigated through the menu screens. Click Close & Apply to complete the data load.

    Build the following Stacked Column Chart:

    Stacked Column Chart
    Stacked Column Chart

    Click the Ellipsis on the upper right hand corner of the chart and select Sort By Total to sort all the items in the bar chart according to how many metals each country won.

    Sort by Total
    Sort by Total

    Great we have made a parameter, loaded multiple excel files, and then made a visual of our data.  Let’s test it out. Save your work and close the PowerBI desktop.  (don’t forget to save your PBIX)

    Next we rename the folder where all the excel files are contained.  Change the name to Olympic Medals.

    Rename File Folder
    Rename File Folder

    Reopen your PBIX file, on the Home ribbon click the Refresh button.  Ooops, since we renamed the folder we now have an error.

    Error Screen
    Error Screen

    To fix this click the bottom half of the Edit Queries button on the home ribbon, and select Edit Parameters.  Then modify the Folder field to include the new name of the other folder Olympic Medals. Click OK to proceed.

    Edit Folder Location
    Edit Folder Location

    A message bar will appear under the Home ribbon.  Click Apply Changes to complete the data load.  PowerBI will reload all your data using the context of the new folder.

    Now this is an elegant solution for sharing PBIX files and their corresponding data files with coworkers.  This is just a simple example of how you would use parameters.  There are a multitude of other really good use cases for using parameters within your queries.  Thanks for reading along, be sure to share if you like this tutorial.