powerbitraining - Tumblr blog

[powerbitraining]

Removing Unnecessary Rows in Power Query

One of the most common  data cleansing  operations performed in Power Query is the removal of unwanted rows. And this is a topic which we cover in almost all of our Power BI training courses. In this blog post, we will look at the key M function used to suppress unwanted rows.

Table.Skip

The first function we will examine is Table.Skip.

Syntax

Table.Skip(

table as table,

optional countOrCondition as any

) as table

Table: The table to be transformed

CountOrCondition: The number of rows to skip or the condition to be met for the rows to be skipped

The function is generated when you click Home > Reduce Rows > Remove Rows > Remove Top Rows. The Remove Top Rows dialog (shown below) allows you to enter the number of rows to be removed.

Power BI then creates a step called “Removed Top Rows” and, behind it, a line of code similar to the following:

#"Removed Top Rows" = Table.Skip(#"Changed Type",2)

(#”Changed Type” is a variable name containing a table reference.)

The M code implementation of Table.Skip created by the interface is fairly basic, in the context of removing rows, in that it provides no flexibility; the number of rows specified is always removed. However, if you are connecting to data which has an unknown number of metadata rows preceding the actual data, you can modify the M code, so that the second argument of Table.Skip is a Boolean test rather than a number.

Thus, for example, if we knew that the first column heading in our data is “Date”, we might say:

#"Removed Top Rows" = Table.Skip(#"Changed Type",each [Column1] <> "Date")

Since we have not got as far promoting headers, the first column will have the default heading “Column1”.

The each causes our Boolean test to be applied to each row of the table. Using this approach, it does not matter if we have one row of metadata to remove or 100 rows; all rows will be removed (starting from the top) until we find the row that contains the “Date” heading.

For removing rows from the bottom, M provides the  Table.RemoveLastN function. The function is generated when you click Home > Reduce Rows > Remove Rows > Remove Bottom Rows. Once again, the interface forces you to specify the second parameter as a number; but you can edit the M code and replace the second argument with a Boolean statement if you wish.

Table.AlternateRows

The Table.AlternateRows function, which is generated by clicking Home > Reduce Rows > Remove Rows > Remove Alternate Rows, removes rows by following a specified pattern.

Syntax

Table.AlternateRows(

table as table,

offset as number,

skip as number,

take as number

) as table

Table: The table to be transformed

Offset: The number of the first row to be removed

Skipped: The number of rows to be removed each time

Take: The number of rows to be kept each time

For example, in the following table, we have daily sales figures, from Monday through to Friday, followed by a row showing the weekly total.

Clearly, for analysis purposes, we would need to suppress the week total rows. Since we have a consistent pattern of rows to be kept and to be removed (keep 5 rows, remove 1 row, keep 5 rows, remove 1 row…), we can use the Table.AlternateRows function to remove the unwanted rows.

Clicking Home > Reduce Rows > Remove Rows > Remove Alternate Rows displays the following dialog.

After completing the dialog, as shown above, we generate the following line of code.

#"Removed Alternate Rows" = Table.AlternateRows(#"Changed Type",5,1,5)

(#”Changed Type” is the name of the variable which references our table.) As you can see the three figures we enter in the Remove Alternate Rows dialog generate the arguments of the offset, skipped and take arguments of the Table.AlternateRows function. Note, however, that the dialog asks us to enter the First row to remove (an absolute value). This will always be one greater than the offset parameter of the Table.AlternateRows function (a relative value).

Table.RemoveRowsWithErrors

The Table. RemoveRowsWithErrors function, which is generated by clicking Home > Reduce Rows > Remove Rows > Remove Errors, removes all rows which contain an error value in any of the specified columns.

Syntax

Table.RemoveRowsWithErrors(

table as table,

optional columns as nullable list

) as table

Table: The table to be transformed

Column: An optional list of columns to be examined (If omitted, all columns are included)

In our weekly sales example, we could generate an error on each of the week total rows simply by changing the type of the Date column to Date, as shown below.

Clicking Home > Reduce Rows > Remove Rows > Remove Errors, with the Date column selected, would then remove all the error rows by creating a line of code like the one shown below.

#"Removed Errors" = Table.RemoveRowsWithErrors(

#"Changed Type",

{"Date"}

)

(#”Changed Type” is a variable containing a reference to our table.)

Note that, even though we are only examining one column, the column name still has to be placed inside a list; since the optional column parameter of the Table.RemoveRowsWithErrors function requires a list. Naturally, if we omit this argument altogether, we would still obtain the same result.

Table.RemoveMatchingRows

Repeating headers often result when you connect to data which is not raw data but, rather, data which has been formatted for output. An example of such data is shown below.

In this example, as we scroll down, we find that row 21 is a repetition of the header row; and, in this case, the pattern repeats.

There are a number of approaches which will be appropriate for different scenarios. We could, for example, filter out the unwanted header repetitions, using the function Table.SelectRows. In the example shown above, since the headers repeat every 21 rows, we could also use Table.AlternateRows.

There is, however, an “industrial strength” function which is ideal for removing only repeating headers, without the risk of accidentally eliminating perfectly sound data rows in the process: the name of the function is Table.RemoveMatchingRows.

The Table.RemoveMatchingRows function allows you to list as many match criteria as you wish, in a very compact format: Column1 = “Column1”, Column2 = “Column2”, and so forth.

Syntax

Table.RemoveMatchingRows(

table as table,

rows as list,

optional equationCriteria as any

) as table

Table The table from which you want to remove rows.

Columns A list containing the criteria you wish to use in determining which rows to remove, using the format ColumnName = Value.

EquationCriteria An optional value that specifies how to control comparison between the rows of the table.

Thus, in the illustration shown above, assuming a table variable called #”Removed Columns”, we would use syntax like the following to remove the repeating headers.

Output = Table.RemoveMatchingRows(

#"Removed Columns",

{[ISO="ISO",

GBP="GBP",

EUR="EUR",

USD="USD",

AUD="AUD",

NZD="NZD",

CAD="CAD"]}

)

The reassuring thing about this approach is that it allows you to test, if necessary, every single column in the table. In order to be removed, a row would have to satisfy all of the criteria listed in the square brackets. In other words, the ISO column would need to contain “ISO”, the GBP column, “GBP”, and so forth.

(Please note, however, that this function can only be entered manually in the M code and has no equivalent command in the Power Query Editors.)

Table.SelectRows

As well as these commands which relate specifically to the removal of rows, Power Query also provides the ability to filter out unwanted rows of data.  Filtering in the Power Query Editor has one distinct advantage over filtering in DAX: any data which is excluded because of a Power Query filter operation will not be loaded into Power BI each time the dataset is refreshed.

The filtering interface is very reminiscent of Excel filtering and the options are very much the same. The same three mechanisms are present: check boxes, a search box and the Filters sub-menu which changes, depending on the data type of the column, to read: “Text Filters”, “Number Filters” or “Date Filters”.

The function which is generated by the various interface options is Table.SelectRows.

Syntax

Table.SelectRows(

table as table,

condition as function

) as table

Table: The table to be filtered

Condition: A function which will be used to evaluate each row in the table

Example

Let’s say we have a column called Revenue and that, using the Number Filter sub-menu, we create a filter condition that Revenue must be greater than 250.

Let us also assume that in our code, a variable called Source contains a reference to our table, the resulting line of code would resemble the following:

#"Filtered Rows" = Table.SelectRows(

Source,

each [Revenue] > 250

)

Here, the Source variable supplies the Table argument; while the Condition argument is supplied by an inline function, consisting of the each keyword followed by the condition to be met.

As you can see, Power Query offers a good number of options for removing unwanted rows of data, both by using the interface and by writing M code.

from G Com Solutions Limited http://bit.ly/2RMzrSK via IFTTT

[powerbitraining]

Using Power BI Dataflows with On-Premises Data

Power BI’s new dataflows allow the centralization of data cleansing and transformation operations, greatly reducing inconsistencies and duplication of effort across organizations. Instead of connecting to data sources from within a Power BI Desktop PBIX file, organizations will now be able to carry out all of their Power Query data connection and transformation centrally in the Power BI service.

Since each dataflow must be created inside of a specific app workspace, the only real way to centralize their use is to create an app workspace (or several if necessary) dedicated to storing dataflows and not designed to be published as an app. Report creators can then be given member access to these dataflow-only app workspaces.

Centralization of data can then be achieved simply by ensuring that all report creators base their reports on dataflows. That way, for each new report created, if a suitable dataflow already exists, it can simply be used; if one does not exist, it can be created and reused in the future.

The following diagram shows how two dataflows might be used to centralize reports created for a manufacturing and sales department, using on-premises data sources. The diagram shows two types of app workspaces: dataflow and content. However, these types are created purely by organizational design; in reality, there is only one type of app workspace in Power BI.

By design, an organization can decide that dataflow workspaces will contain only dataflows: no dashboards, reports, workbooks, or datasets. Since they contain no reports or dashboards, this means that these app workspaces can never be published as apps; either deliberately or accidentally.

Let’s look at how we might create a sales report, working within the framework shown in the diagram above. Let’s say that our organization uses a departmental model in creating app workspaces: one for each organizational department. Thus, we already have a “Sales” app workspace. And let’s also say that we have a workspace called “Organizational Dataflows”, in which we plan to create all our dataflows, and nothing else. Finally, let’s assume that we have an on-premises data gateway installed and configured.

Data Preparation

Firstly, a member of our preparation team creates a Sales data flow inside the “Organizational Dataflows” app workspace, using the key steps shown below.

In the Power BI service, click Workspaces > Organizational Dataflows > Dataflows.

Click Create > Dataflow.

This creates a new, unsaved dataflow. Next, click on the Add new Entities button. (An entity is any element within an organization about which one needs to store data. Each entity will be represented as a table within the data model.)

You will then be given access to all of the data sources to which you can connect. All of the data sources available within Power BI Desktop will eventually be available as data sources for Power BI dataflows.

In this example, we will use Excel as our data source. When we click the Excel button, the Power Query Connection Settings screen appears; and, since the Excel file is stored on a local drive, we are forced to choose an on-premises gateway and enter our credentials.

When we click the Next button, we are given a preview of the available data and allowed to choose the objects we wish to import. In the case of Excel, as in Power BI Desktop, we can import data from tables, worksheets and named ranges.

Clicking the Next button takes us into the online Query Editor which we can assume will be functionally identical to the Power BI Desktop Query Editor when the product is fully developed.

Here, we perform our data cleansing and transformation using a combination of visual commands and M language coding. When we are finished and click the Done button, we are presented with a list of all the entities we have created.

On the right of the name of each entitiy, we have three Action buttons: Edit Entity, Properties and Incremental Refresh. The Edit Entities button allows you to reenter the Query Editor and make further changes. The Properties button allows you to enter metadata about the dataflow, such as a description.

The Incremental Refresh button allows you to force the refresh of an individual entity (the feature works at the entity, not the dataflow, level). It requires that the entity contain a datetime field and that the app workspace containing the dataflow is in premium capacity. This feature can be used to speed up the refresh times of large tables in which only a small proportion of rows are modified between refreshes.

The final step is to save all the changes you have made to the dataflow by clicking the Save button in the top right of the screen and entering a name and description.

If the dataflow contains on-premises data, you will be given the opportunity of setting a refresh schedule using the on-premises data gateway.

You can also perform this step manually at any time by clicking on Settings (the cog icon) > Settings > DataFlows  and activating the Scheduled Refresh option.

In a Power BI Pro capacity app workspace, you can schedule up to 8 refreshes per day; if the app workspace is in premium capacity, you can schedule up to 48 refreshes per day.

Using a Dataflow as a Data Source

In Power BI Desktop, this dataflow can now be used repeatedly, as a definitive data source when building sales reports. At the time of writing, Power BI Desktop is the only viable option, since the Power BI service offers no data modelling or DAX capabilities.

Click Home > Get Data > Power BI dataflows; sign in, if necessary; and click the Connect button.

Unlike the experience of connecting to a Power BI dataset, connecting to a Power BI dataflow is not an all-or-nothing experience. You are able to specify the entities to which you want to connect.

You can also connect to additional data sources within the same report and perform data modelling operations as normal.

In short, Power BI dataflows provide a really useful mechanism for standardizing data preparation and preventing inconsistencies and duplication of effort in organizational reporting.

In this blog post, we have only touched on the use of dataflows with on-premises data.

Check out the following Microsoft blog post and video for a broader introduction to this great new feature.

https://powerbi.microsoft.com/en-us/blog/introducing-power-bi-data-prep-wtih-dataflows/

  from G Com Solutions Limited https://ift.tt/2To60Ug via IFTTT

[powerbitraining]

Using Relationship Columns to Kickstart your Data Modelling

(In this blog post/tutorial, we will use a database called “Event Sales”, which you can download here. If you wish to follow along, you will need to download and UnZip the .BAK file, then use it to restore the EventSales database on an instance of SQL Server.)

When connecting to a relational database from Power BI, it is often the case that you wish to rationalize and flatten the structure of the original database to make it more suitable for reporting purposes; and there are several possible workflows which will help you to achieve this goal.

The Ideal Workflow

Before we get to the use of relationship columns, the main topic of this post, I should stress that it is a feature which is used in “plan B”, which only kicks in when you do not have read/write access to the database to which you are connecting. If you are a DBA of the database in question, then you should use “plan A”, the ideal workflow; so, let’s start by summarizing this ideal workflow.

If you are the DBA of the database to which you want to connect, the ideal workflow is to create a series of views, on the database server; each of which will become a table in the Power BI data model. To illustrate this process, and to keep things simple, we will start with a relational database consisting of 11 tables and flatten this structure down to 4 tables.

In SQL Server Management Studio, let’s look inside the EventSales database which, hopefully, you have downloaded and restored; and, let’s examine the structure of the database by expanding the Database Diagrams folder and double-clicking the dbo.Default diagram.

The database contains 11 tables which are listed below, in alphabetical order.

Categories (CategoryID, WorkshopCategory)

Clients (ClientID, Title, FirstName, MiddleInitial, Surname, Gender, StreetAddress, City, Region, PostalCode, CountryCode)

Countries (CountryCode, CountryName, DialingCode, CountryZoneID)

EventsClients (EventID, ClientID, Revenue, Rating)

EventsData (EventID, EventID, StartDate, CourseID, VenueID)

EventsTutors (EventID, TutorID, TutorFee)

Testimonials (TestimonialID, ClientID, DateFilmed, VideoURL)

Tutors (TutorID, Title, FirstName, MiddleInitial, Surname, Gender, StreetAddress, City, Region, PostalCode, CountryCode, EmailAddress)

Venues (VenueID, VenueName, StreetAddress, City, Region, PostalCode, CountryCode, Latitude, Longitude)

Workshops (CourseID, Duration, CourseName, CategoryID)

ZoneID (ZoneID, Zone)

Now, let us compare this structure with that of the data model which we want to create from the database, in Power BI.

In the data model, we have six tables, only five of which have been imported from the original database:

Clients (First Name, Surname, Gender, City, Region, Postal Code, Country, Zone, Testimonial Date, Testimonial URL)

Events (Event ID, Start Date, Workshop ID, Venue ID, Tutor ID, Tutor Fee, Client ID, Revenue, Rating)

Tutors (Tutor ID, First Name, Surname, City, Region, Postal Code, Country, Zone)

Venues (Venue ID, Venue Name, City, Region, Postal Code, Latitude, Longitude, Country, Zone)

Workshops (Course ID, Duration, Course Name, Category).

The final table in our data model, Dates Table, is a dynamic DAX table created in Power BI:

Dates Table (Date, Year, Month Num, Month)

(Almost all data models in Power BI require a date table containing all the different date and time related categories by which you want to categorize your data.)

This reduction in the number of tables from database to data model is typical of Power BI workflows. As our focus shifts towards reporting and analysis, we denormalize the relational database and introduce a certain amount of redundancy in order to facilitate data analysis.

For example, in the database, since only a small percentage of our clients provide a testimonial, we had a separate Testimonials table which had a one-to-one relationship with the Clients table. In the data model, the key testimonial fields (Testimonial Date and Testimonial URL) have been brought into the Clients table, and Testimonials no longer exists.

As you probably know, the table structure inside our data model is referred to as a star schema. It consists of a single main table, referred to as a fact table; and several supporting tables, all linked to the central fact table, which will be used to categorize the data in the fact table, and which are referred to as dimension tables.

To convert the original relational database structure into a star schema, the EventsClients, EventsData and EventsTutors tables have been combined to form a single fact table called Events. Workshops and Categories have been combined into a single dimension table called Workshops. And the information in the Countries and Zones tables has been imported into the Clients, Tutors and Venues tables, which constitute the remaining dimension tables derived from the original database.

When importing data from SQL Server, you always need to think ahead to the data model you wish to create and devise a strategy which will facilitate its creation. In general, the most efficient approach is to avoid connecting directly to the tables in your relational database. Instead, you can create a series of views, each corresponding to one of the tables which will be present in your data model. In Power BI, you then connect to these views rather than to the original database tables.

Taking this approach makes it easier to update your Power BI data models if modifications are made to the database tables or to the database structure. It also means that you will not have different teams of people, making the same, possibly complex changes to their Power BI datasets to create similar data models.

If most of the transformational logic takes place when the views are created, and report creators connect to the views, all you need to do is to synchronize changes to the database with your views; a much more straightforward task than attempting to synchronize changes to the database with multiple Power BI reports.

In SQL Server Management Studio’s Object Explorer, expand the Views folder. Inside, you will find the five views which can be imported straight into Power BI. For convenience, all these views have been placed inside a schema called “PBI_Sales”.

If you expand the Views section and drill down into the columns, you will notice that the columns have also been given report-friendly names; for example, “Course Name”, rather than “CourseName”.

In a blank Power BI file, let us now import these views. To import data from a SQL Server database, choose Home > Get Data > SQL Server. When the SQL Server connection dialog appears, enter the name of your SQL Server instance in the Server field. (To save typos, you can right-click on the server name in the Object Explorer, choose properties, then copy the entry in the Name field.)

You do not need to enter a name in the Database field; since, if the database field is left blank, when you click the OK button, you will be given access to all the databases on the server. This is particularly useful if you have databases with similar names; since recognition is usually easier than recall.

Let’s set the Data Connectivity mode to Import though this will have no impact on the features we are discussing.

Click on the Advanced options button and make sure that that Navigate using Full Hierarchy is checked.

This causes Power BI to display all objects in easily recognizable groups, making it much easier to find stuff.

Click OK.

Authentication Mode

When you connect to SQL Server, you need to specify the authentication mode you wish to use. There are three options: Windows, Database and Microsoft account.

The default selection, Windows, should be selected if you wish to connect using Windows authentication. If you performed an install of SQL Server Express using the default options, you will be able to connect to the database using your Windows login credentials. Simply leave the default Windows > Use my current credentials activated, as shown below.

In most production scenarios, you will connect using SQL Server authentication. After you select this option, specify a user name and password to connect to your SQL Server instance.

Click the Connect button to advance to the next screen.

If you receive a message regarding encryption (“We were unable to connect to the data source using an encrypted connection. To access the data source using an unencrypted connection, click OK.”), you can click OK to dismiss it. By default, Power BI attempts to encrypt the connection; clicking OK deactivates this default option.

In the Navigator window, use the arrows on the left to expand, first, the SQL Server instance, then EventSales > PBI_Sales (the name of our view). Click in the checkboxes next to each of the five views to activate them, then click the Load button.

Once the data has been loaded into Power BI, click on the Relationships button on the left of the screen, and you will see that Power BI has automatically created relationships between each of the four dimension tables and the main fact table, Events.

Note that this has only happened because of the naming convention we have used: for example, “Client ID” in the Clients table has been associated with “Client ID” in the Events table, and so forth.

Connecting to Database Tables Using SQL Statements

We have seen that, when connecting to SQL Server, it is best to create views and then connect to your views from Power BI, in preference to the underlying tables. However, this may not always be possible; for example, if you are not the DBA of the SQL Server instance, you may not have the authority to create all the required resources.

One way of proceeding in such situations is to use SQL statements. The same statements that you would use to create views in the SQL Server database can be executed from within Power BI.

For example, let us use an SQL statement to combine the three transactional tables (EventsClients, EventsData and EventsTutors) into a single fact table, as we did when creating views, we could execute the following SQL Statement.

SELECT Sales.EventsData.EventID ‘Event ID’ , Sales.EventsClients.ClientID ‘Client ID’ , Sales.EventsClients.Revenue , Sales.EventsClients.Rating , Sales.EventsData.StartDate ‘Start Date’ , Sales.EventsData.WorkshopID ‘Workshop ID’ , Sales.EventsData.VenueID ‘Venue ID’ , HR.EventsTutors.TutorID ‘Tutor ID’ , HR.EventsTutors.TutorFee ‘Tutor Fee’ FROM Sales.EventsClients INNER JOIN Sales.EventsData ON Sales.EventsClients.EventID = Sales.EventsData.EventID INNER JOIN HR.EventsTutors ON Sales.EventsData.EventID = HR.EventsTutors.EventID

To create the data model which we discussed in the previous section, we could proceed in this manner, executing a series of SQL statements equivalent to those used for creating the views which we imported earlier.

However, if we were to do this, we would be disabling a very useful Power BI feature, called query folding, which we will discuss shortly. So, the bottom line is, when connecting to a SQL Server (or similar) database, executing raw SQL statements is the least desirable approach.

Leveraging Query Folding and Relationship Columns

Query folding is the process whereby some, or all, of the transformations you define in the Query Editor are converted into SQL statements and executed by the database server. This can provide a significant performance boost each time the data source is refreshed. However, only SQL statements automatically generated by Power BI are permitted; if you execute your own SQL statement; or, if you use a Query Editor transformation which has no SQL equivalent, no further query folding takes place.

So, let us look at how we might create the Events fact table that we discussed earlier, incorporating the main columns from the EventsData, EventsClients and EventsTutors tables, while taking full advantage of query folding and the relationship columns feature.

Choose Home > Get Data > SQL Server. In the SQL Server database dialog, enter the name of your SQL Server instance in the Server field and “EventSales” in the Database field.

What we will do is to import only the EventsData table; and then use a feature called Include relationship columns, to add the required columns from the related EventsClients and EventsTutors tables. (The Include relationship columns feature uses query folding.)

To understand how Include relationship columns works, let us begin by deactivating it. It is activated by default; so, click on the arrow to reveal the Advanced options section and click in the checkbox to deactivate Include relationship columns.

When the Navigator window appears, click in the checkbox next to the Sales.EventsData table. Look at the preview which appears on the right of the dialog, and you will notice that only the four columns which the table actually contains are displayed. This is what we might call normal behaviour.

Now, press Cancel, so that we can contrast this result with the one produced when Include relationship columns is activated.

Choose Home > Get Data > SQL Server once more. In the SQL Server connection dialog, enter the name of your SQL Server instance in the Server field and “EventSales” in the Database field.

Click on the arrow to reveal the Advanced options section and, this time, click in the checkbox to reactivate Include relationship columns.

Click OK and, when the Navigator window appears, click in the checkbox next to the Sales.EventsData table. This time, on the right of the dialog, you will notice that in addition to the four columns which the table actually contains (EventID, StartDate, CourseID, VenueID), four extra columns are displayed (HR.EventsTutors, HR.Venues, Sales.EventsClients, and Sales.Workshops). (You will need to scroll to the right to see these extra columns.)

These are the relationship columns. Notice how the names of the columns correspond to the names of the four tables which are directly linked to the Sales.EventsData table.

When Include relationship columns is checked, Power BI creates extra columns containing binary data which links to each of the tables directly related to the current table.

In the Query Editor, we can use the Expand button to add any of the columns in the related tables into the current table. So, click on the Edit button to import the data and open the resulting query in the Query Editor.

We now want to add all of the pertinent columns in the HR.EventsTutors and Sales.EventsClients tables into Sales.EventsData. This means that we can begin by selecting and removing the HR.Venues and Sales.Workshops columns, since they do not contain any of the columns that we want to add.

Next, let us click on the Expand button on the right of the HR.EventsTutors column.

In the dialog which appears, deactivate all the checkboxes except for TutorID and TutorFee; since these are the only two options which represent actual data. (We do not need to import the EventID column, since this would simply be a duplication of the existing EventID which is the primary key of the EventsData table.

Let us also deactivate the option Use original column name as prefix; this is only useful where the columns we are importing have some names which are the same as those of the columns in the current table.

Click OK and the TutorID and TutorFee columns are imported into the Sales.EventsData table.

Now, let us do the same for the Sales.EventsClients column. Click on the Expand button on the right of the Sales. EventsClients column. In the dialog which appears, deactivate all the checkboxes except for ClientID, Revenue and Rating; and deactivate the option Use original column name as prefix.

Now, we have a single table containing all the main data which we want to analyse, and we can also benefit from query folding.

Query Folding and Native Queries

As we have said, query folding is the mechanism whereby Power BI creates SQL statements which are equivalent to the transformational steps carried out in the Query Editor; and, on each reload, executes these steps against the SQL Server database, in preference to using the M language to perform the transformations in Power BI.

To monitor Power BI’s query folding decisions, right-click on a Navigation step and choose View Native Query from the context menu.

Power BI displays the Native Query dialog within which is displayed the SQL statement which will be executed when this step is encountered.

Now, right-click on each of the Expanded HR.EventsTutors step and choose View Native Query from the context menu. In the Native query window, you will see the SQL statement which SQL Server will be asked to execute when each step is traversed.

  When creating Power BI reports, it makes sense to ensure that all, or as many as possible, of the operations you perform in the Query Editor will be executed as native queries; since this will help to improve the refresh rates of your reports. The key points to note are as follows.

Query folding will take place whenever a Query Editor transformation can be converted from the underlying M language into an SQL statement.

If you create a query by executing an SQL statement of your own, query folding will not be possible in any M transformations you subsequently perform.

· On the first occasion where there is no SQL equivalent to a Query Editor step that you perform, query folding will cease to occur; not only for that step, but for all subsequent steps.

Creating the Dimension Tables

Hopefully, using the steps outlined above, you will be able to create the remaining four tables required for our data model: Clients, Tutors, Venue and Workshops. For this reason, I will only outline the creation of one of the four; the Clients table.

This will give us a chance to see how the relationship columns feature can be used to incorporate data from tables which are not directly linked to the table to which you are connecting. In this example, we will bring in data from the Zones table into Clients; and, as you can see from the diagram, Zones is not directly linked to Clients; rather, it is indirectly linked, via the Countries table.

Choose Home > Get Data > SQL Server once more. In the SQL Server connection dialog, enter the name of your SQL Server instance in the Server field and “EventSales” in the Database field.

Click on the arrow to reveal the Advanced options section and activate Include relationship columns.

Click OK and, when the Navigator window appears, click in the checkbox next to the Sales.Clients table. On the right of the dialog, you will notice that in addition to the four columns which the table actually contains three extra columns are displayed (Media.Testimonials, Sales.Countries, and Sales.Zones), in other words, one column for each of the three tables directly linked to Sales.Clients.

Click the Edit button to import the data into the Query Editor.

Since we won’t need to bring in any data from the Sales.EventsClients table, let us delete that column.

Click on the expand button on the right of the Media.Testimonials column heading; and activate just the DateFilmed and VideoURL columns.

This gives us two new columns called DateFilmed and VideoURL.

Now, click on the expand button on the right of the Sales.Countries column heading; and activate the CountryName column. However, in addition to the columns in Sales.Countries, you will notice that we also have binary columns which link to tables related to Sales.Countries, including the one that we need, which is Sales.Zones.

Activate Sales.Zones and click OK. Then, finally, click on the expand button on the right of the Sales.Zones; and activate the ZoneName column.

Click OK and we have all the columns that we need in our Clients table. We would then perform all of the Query Editor steps that we need, keeping an eye out to make sure that as many steps as possible can be query folded. (One of our first steps will probably be to rename the columns to make them more report-friendly.)

And so on, for the HR.Tutors, HR.Venues and Sales.Workshops tables…

In summary, when connecting to a relational database, if you are a DBA, then create a view corresponding to each of the tables that you need in your Power BI data model and connect to these views from Power BI.

If you are not a DBA, then you can leverage the Include relational columns and query folding features to achieve the same result from inside Power BI.

from G Com Solutions Limited https://ift.tt/2OdxE30 via IFTTT

[powerbitraining]

The Smart Approach to Passing the MCSA: BI Reporting

To obtain your MCSA: BI Reporting you need to pass two exams: 70-778 (Analyzing and Visualizing Data with Power BI) and 70-779 (Analyzing and Visualizing Data with Excel).

I took the 70-778 last year; and was surprised, when the 70-779 exam was announced, at how much of an overlap there was between the two exams. I took the 70-779 exam in April of this year, shortly after it came out of beta; but, had they both become available simultaneously, I would definitely have prepared for and taken both exams at the same time.

And this is the approach that we are now recommending to students who attend our 5-day Power BI MCSA Certification Training course, which we run in Peterborough once a month.

The overlap between the two exams results from the common BI features found in both Power BI and Excel: namely data modelling using the DAX language; and data connection and transformation using the M language.

The approach that I would recommend is to divide the topics covered in both exams into three categories:

Power BI Topics

Excel Topics

Common topics.

Study and prepare for these topics as a single endeavour, then take both exams on the same day. That way, rather than putting yourself through the preparation and exam cycle twice, you will take full advantage of the “loophole” created by the fact that both exams have so much in common.

The following chart shows an approximation of the percentage of possible topics in each of our three recommended categories.

As you can see, approximately half of the topics which you will need to prepare fall in to the common category; 40% are Power BI topics; and only about 10% are Excel topics. The reason for the disparity between the amount of preparation required for Power BI and Excel topics is a result of the fact that Power BI has so many moving parts.

You will have the same number of questions on Excel as you will on Power BI, but the Power BI questions will be drawn from a much larger pool of possible questions; and, therefore, much more preparation will be required.

If you are a seasoned user of Excel, familiar with all the options relating to pivot tables, pivot charts, slicers and timelines, you may also find that the amount of Excel preparation you require is reduced even more.

In short, why make a task any harder than it needs to be. Microsoft have made the MCSA: BI Reporting a lot easier than it might have been; so, take advantage of this fact; and prepare for the 70-778 and 70-779 together, as if they were a single exam.

from G Com Solutions Limited https://ift.tt/2MJ9I6u via IFTTT

[powerbitraining]

Power Training for Microsoft Excel Users

Microsoft Excel is an essential tool for most modern businesses, providing a dazzling array of features for manipulating and analysing organisational data. The concept of power training for Excel users is not new; and the traditional route to becoming an Excel power user has been to develop a fairly large repertoire of advanced techniques; such as the creation of complex formulas and VBA macros. However; thanks to the significant enhancements made to Excel by Microsoft; it is now possible for all of your Excel users to become power users!

Our Excel power training courses show delegates who have an intermediate to advanced knowledge of Excel how they can become power users simply by mastering a series of powerful new features which have been available in Excel for a few years now, but of which most users are still blissfully unaware. G Com Solutions run Excel power training courses every month, at our training centre in Peterborough; and we can also provide on-site training, anywhere in the UK.

Excel’s Hidden Gems

In a few years’ time, it is probable that these features will have become familiar to the majority of Excel users. However, today, they are still hidden gems waiting to be discovered.

Many companies using Excel find that, as they create data, they often end up using Excel as an informal database management system. To analyse this data, tie it all together and create reports from it, requires advanced Excel techniques; and thus, was born the Excel power user; someone who develops a bag of tricks consisting of advanced formulas (usually including VLOOKUP and SUMIFS functions) and VBA macros.

One of the problems with this situation is that the solutions developed by one power user to solve a given problem may be different to those created by another power user to solve the same problem. Thus, many organisations inherit Excel models which only one or two people fully understand. With the release of the Power Pivot and Power Query add-ins and their subsequent incorporation into Excel’s built-in feature list, Microsoft have made available a series of tools which are capable of transforming any competent Excel user into a power user. And the great thing is, the status of power user is bestowed upon them as a result of the tools they use, not as a result of designing their own personal bag of tricks.

Power Pivot and Power Query allow users to work with very large datasets, often derived from disparate sources. Power Pivot allows you to connect to your data and to specify rules for transforming the data as it is being imported in all kinds of useful ways. Most of these transformations can be performed with a series of user-friendly visual tools.

Many of the operations which Power Query allows the user to perform on their raw data could only be done as efficiently and quickly by writing and maintaining complex macros; a skill which relatively few Excel users master. True, anyone can use the macro recorder; but to

create scalable, working macros, users have to learn to program; and not many Excel users have the time or inclination to become fully-fledged programmers.

Excel Power User Versions

Excel’s new power features are not available in all versions of Excel; the following table summarizes availability in different versions.

Excel 2016 (Built in)Excel 2016 Standalone. Excel 2016: Office 365 ProPlus, E3, E4 and E5 editions.Excel 2013 (Built in)Excel 2013 Standalone. Excel 2023: Office 2013 Professional Plus edition.Excel 2010 (Add-ins)Available as a series of add-ins which can be downloaded and installed free of charge.Excel 2007 or earlierExcel’s power user features are not available in Excel version 2007 or earlier.

Powerful Tools all your Staff Can Master

The purpose of the Excel power training courses by G Com Solutions is to allow any competent Excel user to become a power user. Delegates are shown how to leverage the most powerful tools in Excel, to bring together disparate related data sources and create insightful reports from them. And the great thing is that these tools are no more difficult to learn than Excel’s mainstream features.

Getting your people trained on Excel’s most sophisticated analysis tools will bring significant rewards in increased productivity and business intelligence. Modern businesses typically accumulate thousands, or millions, of rows of important data every year; and encouraging your staff to use Excel’s power user tools will broaden the range of questions you can answer about your data.

Power Training for Microsoft Excel Users: Level 1

This course is aimed at intermediate and advanced users of Excel who are only familiar with Excel’s “traditional” features. It aims to show delegates how to make the transition from over-reliance on Excel’s VLOOKUP function to working with tabular information from multiple sources using Excel’s supercharged Power Query and Power Pivot tools. Delegates will be introduced to the Excel data model and will learn how to create pivot tables and dashboards which visualize data from multiple related sources.

Introduction

Excel’s new frontiers; Understanding Excel’s new power components; New ways of working in Excel; Limitations of VLOOKUP models; Connections and queries versus automation; Activating Excel’s power tools

Tabular data

What is tabular data; The benefits of working with tabular data; Features and benefits of Excel tables; Other tabular data sources

The Excel Data Model

What is the data model? Benefits of using the data model; Key elements of the Excel data model; Building multi-table solutions

Tables Queries and Connections

Understanding Excel tables; Creating A Query from An Excel Table; Working with linked tables; Adding linked tables to the data model; Reviewing connections; Creating a query from a named range; Creating a query from an ordinary worksheet range

Getting Started with Power Query

What Is Power Query? Installing and Activating Power Query; Overview Of Data Sources; Overview Of Power Query workflows

Query Basics

Creating A Query; Understanding Power Query Steps; Renaming A Query; Renaming steps; Why no Undo? Changing settings; The Close & Load Command; The Close & Load To Command

Modelling Data

The Power Pivot add-in; Power Pivot overview; Navigating the Power Pivot interface; working with multiple tables; creating relationships

Creating Pivot Table Reports

Creating a Power Pivot pivot table; Using fields from related tables; Formatting pivot table data; Creating drill-down experiences; Displaying aggregate values; Displaying multiple aggregations; Displaying percentage breakdowns.

Creating Dashboards

Designing dashboards; Using slicers to filter data; Using timelines to filter data; Specifying report connections; Creating pivot charts

Power Training for Microsoft Excel Users: Level 2

This one day course is designed for users who are already familiar with Excel’s Power Query and Power Pivot tools. It shows users how to connect to a wide variety of data sources and create data models which include DAX calculated columns and measures. Delegates will also be shown how to create rules for transforming data as it is added to the data model and how to create Power View reports.

Connecting to Data from outside Excel

Getting native Excel data; Getting data from relational databases; Getting data From .CSV And .TXT files; Getting tabular data from a web page

Transforming Columns of Data

Removing unwanted columns; Renaming columns; Reordering columns; Changing the case of columns; Trimming and cleansing columns; Extracting characters from columns; Performing mathematical operations on columns; Splitting columns; Concatenating columns; Converting columns to Boolean values; Transforming date columns

Transforming Rows

Promoting row headers; Removing unwanted rows of data; Removing duplicate rows; Removing rows which contain error values; Replacing error values; Counting rows

Performing other Data Transformations

Using Find and Replace; Filtering out unwanted rows; Changing data types; Unpivotting columns of data; Pivotting rows of data

Creating Calculated Columns

DAX and Excel formulas compared; When to create calculated columns; User-friendly DAX functions; Using IF and SWITCH; Using the RELATED function

Creating Measures

Implicit and explicit measures; Creating measures best practices; Referring to measures in other measures; How measures are stored; Using DAX helper measures

Understanding the DAX Language

Understanding the DAX formula evaluation context; Understanding the Row context; Understanding the filter context; Using SUMX and COUNTX; Using RELATEDTABLE; Using COUNTROWS and DISTINCTCOUNT

Understanding the CALCULATE function

CALCULATE and SUMIFS compared; Why CALCULATE is so important in DAX; Understanding the CALCULATE syntax; How CALCULATE alters the filter context; Using CALCULATE with Boolean filter statements; Using CALCULATE with the ALL, ALLEXCEPT and ALLSELECTED functions; Using CALCULATE with the FILTER function

Creating Power View Reports

Power View basics; Using the filters pane; Using cards for big picture insights; Tables and matrices; Creating line charts; Creating pie charts; Creating scatter charts; Using Power View maps

Power Training for Microsoft Excel Users: Level 3

This course is designed for experienced users of Excel’s Power Pivot and Power Query tools and provides insights into the advanced aspects of these powerful technologies. Delegates will be shown how to write their own functions in the M language which underlies Power Query; and to create complex DAX formulas using advanced time intelligence functions.

Advanced Data Transformation

Grouping and aggregating rows of data; Creating conditional columns; Creating columns by referencing existing columns; Appending data with common column headers; Merging data with common rows; Combining all files in a folder

The Power Query Formula Language

Using the Formula Bar; Using The Advanced Editor; Overview Of The M Language; Exploring M using #shared

Understanding Automatically Generated M Code

Excel.Workbook; File.Contents; Table.PromoteHeaders; Table.TransformColumnTypes; Table. AddColumn; Table.ReplaceValue; Table.Skip; Table.RemoveLastN

Creating custom functions in M

Defining a function; Defining input parameters; The goes-to operator; Defining the function body; Using optional parameters; Calling functions

Iteration Techniques

Benefit of generating lists; Generating lists of numbers; Generating lists of dates; Generating alphanumeric lists; Using the Each function; Applying a function to a list of files

Advanced DAX

Using disconnected parameter tables; Creating parameter measures; Overview of DAX time intelligence; Creating a date table; Using DATEYTD and its variations; Using SAMEPERIODLASTYEAR; Using PARALLELPERIOD; Using FIRSTDATE and LASTDATE; Using DATEADD; Using DATESBETWEEN

Hierarchies

Creating hierarchies; Using RELATED to assemble columns; Adding, removing and reordering columns; Using a hierarchy on a pivot table

KPIs

Overview of KPIs; Creating KPIs; Base value; Target value; Status threshold; Creating measures for KPIs; Displaying a KPI on a pivot table

Creating 3D Map Reports

3D Map data requirements; Understanding scenes and tours; Adding fields to a report; Adding a timeline; Using themes; Creating scenes; Setting scene effect options; Using the Tour Editor; Creating heat maps

Sharing Queries

Getting an Azure Data Catalog subscription; Getting a Power BI subscription; Using Send to Data Catalog; Sharing queries with specific groups; Using Data Catalog Search; Using My Data Catalog

Our charge for supplying customized training in Aberdeen is £850, per day. Our price for delivering on-site courses in Accrington/Rossendale is £750, per day. Our charge for delivering Microsoft Excel power training courses in Barnsley is £750, per day. Our price for running Microsoft Excel power training courses in Basildon is £695, per day. Our fee for delivering on-site power training in Basingstoke is £695, per day. Our charge for supplying customized training in Bedford is £695, per day. Our price for delivering on-site courses in Belfast is £850, per day. Our price for delivering on-site training courses in Birkenhead is £695, per day. Our price for supplying in-house courses in Blackburn is £750, per day. Our price for supplying on-site power training in Blackpool is £750, per day. The cost of supplying customized training in Bournemouth is £750, per day. The charge for running customized training courses in Brighton and Hove is £750, per day. Our fee for delivering in-house training courses in Bristol is £750, per day. Our price for running Microsoft Excel power training courses in Burnley is £750, per day. The cost of supplying in-house training courses in Burton-upon-Trent is £750, per day. The cost of supplying on-site power training in Cambridge is £695, per day. The cost of supplying customized courses in Cardiff is £825, per day. The cost of supplying customized training in Chelmsford is £695, per day. The fee for running on-site power training in Cheltenham is £750, per day. Our fee for delivering customized training in Chesterfield is £750, per day. The cost of supplying in-house courses in Colchester is £695, per day. Our price for running customized courses in Coventry is £695, per day. Our price for delivering on-site training courses in Crawley is £695, per day. The cost of supplying on-site training courses in Derby is £695, per day. Our charge for supplying on-site training courses in Doncaster is £750, per day. The fee for running customized training in Dundee is £850, per day. Our charge for supplying customized courses in Eastbourne is £695, per day. Our price for running customized training in Edinburgh is £825, per day. Our fee for delivering customized training in Exeter is £750, per day. The cost of supplying on-site training courses in Farnborough/Aldershot is £695, per day. Our price for running customized courses in Glasgow is £825, per day. Our charge for supplying in-house courses in Gloucester is £750, per day. Our price for running on-site power training in Greater London is £695, per day. Our charge for supplying customized training courses in Grimsby is £750, per day. Our charge for delivering on-site training courses in Hastings is £695, per day. Our charge for delivering on-site power training in High Wycombe is £695, per day. Our charge for delivering Microsoft Excel power training courses in Ipswich is £695, per day. Our charge for delivering on-site training courses in Ireland is £850, per day. Our charge for supplying on-site courses in Kingston upon Hull is £750, per day. The cost for running customized training courses in Leicester is £695, per day. Our price for running customized training courses in Lincoln is £695, per day. Our price for running customized training in Liverpool is £750, per day. Our charge for delivering customized training in Luton is £695, per day. Our price for supplying in-house courses in Maidstone is £695, per day. Our charge for supplying customized training in Manchester is £750, per day. The fee for running on-site training courses in Mansfield is £750, per day. The cost for running customized training in Medway Towns is £695, per day. Our price for supplying on-site courses in Milton Keynes is £695, per day. The cost for running customized courses in Newport is £750, per day. Our price for delivering customized training in Northampton is £695, per day. The charge for running customized courses in Norwich is £695, per day. The charge for running customized training in Nottingham is £695, per day. The cost for running customized training in Oxford is £695, per day. Our fee for delivering on-site training courses in Peterborough is £695, per day. Our fee for delivering on-site courses in Plymouth is £750, per day. Our price for delivering on-site power training in Poole is £750, per day. The cost of supplying customized training in Preston is £750, per day. Our price for running Microsoft Excel power training courses in Reading is £695, per day. Our price for running customized training courses in Sheffield is £695, per day. Our price for delivering in-house courses in Slough is £695, per day. The cost for running in-house courses in South Hampshire is £695, per day. Our charge for delivering customized training courses in Southend-on-Sea is £750, per day. Our price for running customized courses in Stoke-on-Trent is £695, per day. Our price for delivering on-site power training in Sunderland is £750, per day. Our charge for supplying customized training in Swansea is £825, per day. Our price for supplying on-site courses in Swindon is £695, per day. Our fee for delivering on-site power training in Teesside is £750, per day. The cost for running customized training in Telford is £750, per day. Our price for running Microsoft Excel power training courses in Thanet is £750, per day. The cost for running customized training courses in The West Midlands is £695, per day. Our charge for supplying customized training courses in Torquay is £750, per day. Our price for running on-site training courses in Tyneside is £750, per day. Our price for supplying in-house training courses in Warrington is £750, per day. The cost for running on-site courses in West Yorkshire is £695, per day. Our fee for delivering on-site power training in Wigan is £750, per day. The charge for running customized training in Worcester is £750, per day. Our price for running in-house courses in York is £750, per day.

The post Power Training for Microsoft Excel Users appeared first on G Com Solutions Limited.

from G Com Solutions Limited https://ift.tt/2Ij42hx via IFTTT

[powerbitraining]

Customizing In-House Power BI training

If you are considering Power BI training, one of your first decisions is choosing between sending your staff on a scheduled training course and arranging in-house Power BI training. There are pros and cons to both approaches.

Booking a place on a public Power BI training course

One of the main benefits of booking a place on a scheduled Power BI training is that your staff will not all need to attend the training on the same day. This may become a factor if several key people from the same department need to attend the training and they cannot all be absent from the workplace at the same time. You can send different people on public courses at different times.

One of the main drawbacks of public courses is, however, that the course content will follow a pre-set schedule and cannot be modified to suit the requirements of your company.

Arranging in-house Power BI training

The main benefits of booking in-house Power BI training, as against sending your people off to a training centre to attend a public course, is that your training can be customised to match the specific needs of your organisation. So, when you book an in-house training course, be sure to provide G Com Solutions with a full briefing on your requirements.

Based on the topics proposed by G Com Solutions and by your own awareness of how you will use Power BI, produce a list of essential topics which you feel need to be covered. Discuss the matter with the staff members who will be attending the course, their managers or people in your company who already have some Power BI or business intelligence experience.

Naturally, if you are to host a training event, you will need to have suitable facilities. You will need to find a suitable room where you can realistically conduct a training session and where the trainees can concentrate on learning without being interrupted or distracted. If your organisation does not have a dedicated training room then a meeting room can be modified for the purpose.

Delegates should be able to see the trainer from their seat without having to perform any contortions. Each delegate should also have the use of their own laptop for the entire course and enough space to use a keyboard and mouse.

Ideally, you will also need to provide the trainer with some method of projecting the screen of a demonstration laptop onto a large screen, be it via a projector or big screen TV. If your company does not own a projector, one can be hired for around £25 per day. A whiteboard and pens are also useful.

A large screen is particularly important if you plan to have a full-house session with eight to ten delegates. (If you plan to have more than 8 to 10 delegates attending a training course, then you should really regard the session as more of a seminar, since, with larger numbers, it becomes harder for delegates to receive the individual attention which should be part of the training experience.)

For your training to be effective, all staff must be available for the entire duration of the course and must not be interrupted by other members of staff. Ideally, they should be treated as absent from the office until their training ends. Equally importantly, they should be motivated to do the training and agree that it will benefit them and enable them to work more effectively.

Customising the training

To obtain the maximum benefit from in-house Power BI training, it is always a good idea to customise the content of the course to suit the requirements of the staff who will be attending. Provide examples of your data to G Com Solutions before the course so that they have a good idea of the kind of documents your staff will need to create or modify. (Naturally, you should first remove any sensitive or confidential information!).

A good software training session will give users plenty of chances to practice the skills they are being taught. Ask G Com Solutions to incorporate your own data into the practical exercises given to trainees on the course. For example, if your staff will be analysing sales data, have the trainer provide them with exercise involving the creation of sales analysis reports, using the visualizations and DAX formulas which they will need for their own work.

The approach that we take on our courses is to have each delegate open a completed Power BI report in a web URL (generated using the Publish to Web feature), provide them with the necessary raw data, and then ask them to create it from scratch. We find that this is a great way to ensure that they are mastering all the key techniques that they will need for their own reports.

Installing Power BI Desktop

Microsoft’s free Power BI Desktop should be loaded on each person’s machine and, ideally, all delegates should be using the same version of the software. It is also very important, with this particular piece of software, that the latest version of the application should be installed. Power BI Desktop is updated every month, like clockwork; and powerful new features are being added all the time. If your IT department install Power BI Desktop, on the laptops being used for training, several months (or even weeks) prior to the training; the version available may not have some of the features being discussed during the training.

There are two ways of installing Power BI Desktop: as a direct download, or from the Windows Store.

To download the most recent version of Power BI Desktop, simply perform a web search for “Download Power BI Desktop”; or visit the following Microsoft URL:

https://powerbi.microsoft.com/en-us/desktop/

Choose the 64-bit version of the application to benefit from the superior performance and greater compatibility with large datasets. Having download the MSI package, double-click the downloaded file and follow the installation instructions. Power BI Desktop is installed as an application and does not require internet access to run. However, you will need to login to your Power BI service account when you wish to publish your reports to the cloud.

In many organisations, installing Power BI Desktop on a laptop requires admin privileges which are only possessed by IT staff. The alternative method of installing Power BI, very often, does not require admin rights; and this is to install Power BI Desktop from the Windows Store. However, this method is only an option for organizations who are running Windows 10.

To download Power BI Desktop onto a laptop running Windows 10, the user simply needs to launch the Microsoft Store app and perform a search for “Power BI Desktop”. Click on the app, when it is displayed in the search results; then click the “Install” button.

As well as the possibility of not requiring an admin login to install the application, there is another key benefit to obtaining Power BI Desktop from the Windows Store: you can always rest assured that you are using the latest version; since the app will be automatically updated by Windows, as soon as a new version becomes available.

Obtaining Power BI licenses

When you book in-house Power BI training from G Com Solutions, Power BI Desktop is the only Power BI product that you need to have in place before the training session; since we will provide everything else that is required for the training. We provide each delegate with the login credentials of a Power BI Pro account in the Power BI tenant which we use for training.

However, after the training, it is important that each delegate continues to use the product; and, to do so, they will each require a Power BI Pro license. This can be obtained in two ways: as part of a trial or using the full product. If you wish to have the training prior to rolling out the product, you can set up a trial Power BI tenant account, which will give you a 60-day free trial period in which to evaluate the product.

The post Customizing In-House Power BI training appeared first on G Com Solutions Limited.

from G Com Solutions Limited https://ift.tt/2Gq8XRu via IFTTT

[powerbitraining]

Another test post

Testing 1

This is just a test post. Remind me to delete it. This is just a test post. Remind me to delete it.This is just a test post. Remind me to delete it.This is just a test post. Remind me to delete it.This is just a test post. Remind me to delete it.

Testing 2

This is just a test post. Remind me to delete it.This is just a test post. Remind me to delete it.This is just a test post. Remind me to delete it.This is just a test post. Remind me to delete it.This is just a test post. Remind me to delete it.

 This is just a test post. Remind me to delete it. This is just a test post. Remind me to delete it. This is just a test post. Remind me to delete it. This is just a test post. Remind me to delete it. This is just a test post. Remind me to delete it. This is just a test post. Remind me to delete it.

The post Another test post appeared first on G Com Solutions Limited.

from G Com Solutions Limited https://ift.tt/2GYAZjY via IFTTT

[powerbitraining]

The Excel Ribbon

The Excel  Ribbon is one of the first topics covered on our Introduction to Excel. Ribbon, first introduced with Microsoft Office 2007, is the key component of the Excel interface and offers access to most of its features, facilities and utilities. However, as is now the case with all sophisticated software packages, there are often several other ways of accessing Excel’s features and bypassing the Ribbon.

The Ribbon consists of a series of tabs, each of which offers commands related to a certain theme. These commands are further subdivided into Groups, with the name of each Group displayed at that the bottom of the Ribbon.

The Ribbon is a very honest and open interface: basically, everything is laid out for you: the simple, the intermediate, and the advanced; it’s all there; nothing is hidden. There follows a brief description of each of the Ribbon Tabs and the Groups of controls found in each one. I’d recommend that you take some time reviewing the various Tabs: this will help in the process of demystifying Excel and making it seem less daunting.

Watch out for the launcher buttons

In the bottom right-hand corner of some Ribbon Groups, you will notice a tiny icon consisting of a rectangle with an arrow. As you browse through the Ribbon Tabs, keep an eye out for these rather inconspicuous buttons. They are called launcher buttons and what they usually launch are the rich and powerful Excel dialog boxes, many of which have been part of Excel for ages. For example, on the Home Tab, the Clipboard, Font, Alignment and Number Groups each contain a launcher button (See figure 1-19, below).

Launcher buttons are found in the bottom right of some Ribbon Groups and normally display a dialog box containing a detailed series of options.

Clicking on these launcher buttons will be particularly reassuring for those of you who have upgraded to Excel 2016 straight from version 2003, or earlier; since it will help you to realize that your existing skill set is still very relevant to this latest version.

The Home Tab

The Home Tab of the Excel Ribbon contains those essential editing and formatting commands which one uses all the time.

The Clipboard Group offers the long-winded way of performing the common clipboard operations: Cut, Copy and Paste. (Most people prefer to use keyboard shortcuts (Control-X, Control-C and Control-V) or to choose these commands from the right-click menu).

The Clipboard Group contains a launcher button which displays the Office Clipboard, in a task pane on the left of the screen. The Office Clipboard keeps track of up to 24 copied items at a time and allows you to paste any of them. It can come in quite handy when building complex Excel formulas.

The Font Group allows you to change the stylistic appearance of the text you enter into your spreadsheets such as its font, size and colour.

The Font Group contains a launcher button which displays the Format Cells dialog box, with the Font tab activated.

The Alignment Group provides commands for rotating and wrapping text—very useful for long headings—merging cells and controlling both horizontal and vertical alignment.

The Alignment Group contains a launcher button which displays the Format Cells dialog box, this time with the Alignment tab activated.

The Number Group provides options for data formatting—for example, controlling the number of decimal places shown with numbers, and the way in which dates are to be displayed.

The Number Group contains a launcher button which displays the Format Cells dialog box, this time with the Number tab activated.

The Styles Group provides automatic formatting options—in contrast to the manual formatting offered by the Font Group. These options include Excel’s powerful conditional formatting commands.

The Cells Group provides commands for manipulating cells, columns and rows: such things as inserting, deleting and specifying widths and heights. It also contains options for hiding, moving and protecting the sheet as a whole.

The Editing Group has a variety of popular commands for entering and modifying cell data. The AutoSum tool is used to rapidly create simple formulas with just one or two clicks. The Sort & Filter button can be used for quickly sorting data by the column in which the cursor is positioned. The Group also contains options for deleting, finding and replacing data.

The Insert Tab

The Insert Tab of the Excel Ribbon contains options for superimposing elements onto a worksheet which differ from the normal cell contents—items such as charts, imported images and vector graphics.

The Insert Tab is mainly used for creating graphic elements which are not contained by the cells of the worksheet.

The Tables Group allows you to create Excel tables (which are used to store columnar data) and pivot tables (which are used for data analysis). Although pivot tables store their data within worksheet cells, they then prohibit normal access to those cells; the cells within a pivot table cannot be individually edited.

The Illustrations Group contains options for adding bitmapped and vector graphics to Excel. Images can be inserted from a local drive or via the web: there is even the option of searching for images using Bing. Vector images can be drawn within Excel using the Shapes and Smart Art buttons. The Screenshot button allows you to grab any part of any window currently open on your computer screen.

The Apps Group lets you access Microsoft apps for Office solutions. Apps for office resemble iPhone and Android apps: they are essentially web pages which Excel (or any other Office program) can host; and which provide some useful functionality or service not native to the program.

The Charts Group allows you to add Excel charts and pivot charts to your worksheet. A pivot chart is simply a chart which uses a pivot table as its data .source, rather than a regular Excel data range.

The Charts Group contains a launcher button which displays the same dialog as is shown when you click the Recommended Charts button.

The Reports Group contains just one item: the Power View button. Power View is a built-in Excel add-in which just needs to be activated. It allows you to create stunning business visualizations driven by the new Excel Data Model.

The Sparklines Group allows you to insert sparklines in your worksheets. Sparklines are miniature charts which are displayed within cells, usually adjacent to the data they plot, and which provide handy visual clues regarding trends.

The Filters Group contains buttons for adding slicers and timelines to tables, pivot tables and pivot charts. Both of these elements provide a highly interactive mechanism for filtering structured data. Slicers can be used with any data type; while timelines can only be used to filter dates.

 The Links Group contains only the Hyperlink button. Hyperlinks work in pretty much the same way in all of the Microsoft Office products. In Excel, you can place a hyperlink on any cell, or range of cells; which takes the user to another worksheet, opens a different workbook, creates a new email message, or goes to a web URL.

The Text Group allows you to insert text based elements which reside on the object layer, above the worksheet cells. You can add text boxes, headers and footers, Word Art, signature lines and use Microsoft’s Object Linking and Embedding (OLE) technology to insert objects.

The Symbols Group has options for inserting equations and symbols. Symbols can be inserted like any other character, inside a cell. However, equations are inserted in a text box on the object layer, on top the worksheet cells. Excel provides a sophisticated Equation toolbar for editing equations, containing dozens of mathematical and scientific symbols and constructs.

The Page Layout Tab

The Page Layout Tab of the Excel Ribbon contains options for controlling the appearance of your worksheets as a whole. Here, you’ll find settings relating to margins, orientation, size of paper, and the area of the worksheet to be printed. You will usually go to the page layout Tab before printing worksheets.

The Page Layout Tab contains all of the options relating to Page Setup.

The Themes Group allows you to make use of the built-in Themes feature now common to all Microsoft Office products. Themes provide a simple and quick method of controlling the format of an entire document. Choosing options from the Themes, Colors, Fonts and Effects drop-down menus, found in the Themes Group, can change the appearance of worksheet cells and graphic objects in one hit, using tasteful and well-coordinated tones.

The Page Setup Group contains the commands that you normally need to use before printing a document: for example, changing the margins, orientation and page size; or specifying a print area (whereby only a portion of the worksheet will be printed).

The Page Setup Group contains a launcher button which displays the Page Setup dialog box with the Page tab activated.

The Scale to Fit Group simply contains the commands found in the Scaling section of the Page tab of the Page Setup dialog.

The Scale to Fit Group contains a launcher button which displays the Page Setup dialog box, also with the Page tab activated.

The Sheet options Group contains options for hiding and showing gridlines and column and row headings, both on screen and at print time.

The Sheet Options Group contains a launcher button which displays the Page Setup dialog box, this time with the Sheet tab activated.

The Arrange Group is encountered in several different Tabs. It provides a series of commands allowing you to align and arrange objects: such as Bring to Front, Send to Back and Group.

The Formulas Tab

The Formulas Tab of the Excel Ribbon contains commands for dealing with one of the most powerful features of Excel: formulas and functions. Here you can insert functions, track down errors in formulas and control the way in which Excel deals with formulas and carries out its calculations.

The Formulas Tab contains Excel’s main options for creating, displaying and auditing formulas and determining how the program performs worksheet calculations.

The Function Library Group allows you to insert any of Excel’s several hundred functions into your formulas. The functions are divided into categories, with the final category (More Functions) displaying, in a series of sub-menus, those categories which do not have their own drop-down menu.

This group does not need a launcher button, since the main dialog associated with formulas (Insert Function) can be accessed via the final option in each of the drop-downs.

The Defined Names Group deals with the creation and management of names. Names are an Excel resource which allow you to associate a text label (the name) with a range of cells, a formula or a static value (such as a tax rate). When creating formulas, you can use the name instead of the value that it represents. This adds clarity to your formulas and offers you a powerful mechanism for updating several formulas by making a change to the value associated with a single name.

The Formula Auditing Group options are designed to help you find errors in your formulas. Using these options, you can quickly highlight cells which are referred to by a formula and vice versa, or step through a complex formula, statement by statement.

The Calculation Group has commands for controlling how and when Excel performs calculations. In workbooks that contain many formulas—especially formulas that link to external workbooks—the need to calculate the result of formulas can have a negative impact on the usability of a workbook. Hence, this group offers the ability to make calculation manual, so that Excel will only perform calculations when you click the Calculate Now button.

The Data Tab

In the Data Tab of the Excel Ribbon you will find commands for importing information into Excel from sources such as external databases; sorting information and also filtering (in other words returning only data which matches certain criteria).

Here you’ll also find advanced tools for consolidation, validating data and performing “What-if” analysis. Also, in the outline section, there are commands for assigning different levels to the various columns and rows of a worksheet. Excel’s powerful outlining commands make large worksheet easier to manage and to navigate.

The Data Tab contains Excel’s powerful data analysis tools.

The Get External Data Group contains commands which are used to import data from external sources into the Data Model, which can then be used to perform data analysis and visualization using pivot tables, pivot charts and the Power View add-in. These same commands can also be found in the PowerPivot add-in, which offers you a host of additional features for modelling imported data sets.

The Connections Group has options for working with existing connections. The Connections button displays the Workbooks connections dialog where you can review and remove connections. The Refresh All button allows you to replace obsolete copies of imported data, permanently stored within the data model, with the latest version from the original source.

The Sort & Filter Group commands allow you to perform basic and advanced sorting and filtering on tabular data. The Sort A to Z and Sort Z to A buttons allow one-click sorting by a single column; while the Sort button displays the Sort dialog allowing you to perform multi-column sorting, as well as sorting by colour and custom lists.

Similarly, the AutoFilter button allows rapid filtering of information according to preset criteria; whereas the Advanced Filter command allows you to isolate data which matches more complex criteria.

The Data Tools Group provides access to specialized, built-in Excel utilities each of which performs a useful data manipulation task. For example, the Text to Columns command allows you to parse data which currently occupies a single column (typically after being imported into Excel) into separate columns. The group also contains the powerful Remove Duplicates and Flash Fill commands.

The What-if Analysis tools (Goal Seek, Scenarios and Data Tables) are data analysis tools which have been part of Excel’s arsenal for ages.

The Outline Group is the home for another long-standing Excel feature called grouping and outlining: the assignation of a hierarchy of levels to different rows and columns. This capability is very useful for making large worksheets easier to manage and manipulate.

The Review Tab

The principal theme of the Review Tab is checking, protection and the multi-user sharing of workbooks. It is here that you will find Excel’s spell-checker, commands enabling users to add comments to a worksheet, options for protecting both worksheets and workbooks and for sharing workbooks with other users across a network.

The Review Tab contains options for the protection and sharing of workbooks.

The Proofing Group contains the familiar Spell-checker and Thesaurus commands; as well as a Research tool which allows you to perform searches in several local and online references, including Bing and Encarta.

The Language Group is a subset of the Research tool which allows you to translate common words and phrases from, or into, over thirty different languages.

The Comments Group provides the ability to annotate worksheets with notes; both for your own benefit and as a workgroup facility, for sharing your thoughts on worksheet content with colleagues. The Group has buttons for creating, editing, deleting, showing and hiding comments.

The Changes Group houses the most powerful commands in the Review Tab. The Protect Sheet and Protect Workbook commands can be used to restrict access to specific cell ranges, control user modifications, and to protect the workbook structure. There are also commands for the multi-user sharing of workbooks and the tracking of changes made by different users to the same workbook.

The View Tab

The View Tab houses controls relating to the way in which your workbooks and worksheets are viewed on screen. It contains commands for zooming in and out on your work and for displaying several worksheets and workbooks simultaneously.

The View Tab contains options which allow you to control the on-screen appearance of your worksheets.

The Workbook Views Group buttons change the way your worksheets are displayed: Normal view is optimized for speed; Page Break Preview allows you to customise pagination; Page Layout gives an accurate preview of how documents will print; and Custom View lets you save on-screen and page setup attributes.

The Show Group has commands which allow you to show and hide key elements of the Excel interface such as row and column headings, gridlines and the formula bar.

The Zoom Group buttons control the magnification at which you view your work. You can, for example, highlight a range of cells and click the Zoom to Selection button to have Excel automatically zoom in or out so that the selected range fills your screen.

The Window Group offers options which are useful when working with several different documents. For example, the Arrange All commands will reduce and size all of the currently open windows, so that you can see their contents simultaneously.

The Macros Group has a single drop-down control which allows you to view all of the macros in any of the open workbooks, as well as recording new macros. (Macros are automated series of commands which can be executed automatically at the click of a button.

The post The Excel Ribbon appeared first on G Com Solutions Limited.

from G Com Solutions Limited https://ift.tt/2GnuSHZ via IFTTT

[powerbitraining]

Take our Power BI Essentials Quiz

Are you an aspiring Power BI power user? Do you know all the essentials of Power BI? Are you thinking of taking the exam 70-778?

If your answer to these three questions is yes, then you should ace our Power BI essentials quiz. Are you ready?

The post Take our Power BI Essentials Quiz appeared first on G Com Solutions Limited.

from G Com Solutions Limited http://ift.tt/2t0b9J2 via IFTTT

[powerbitraining]

Power BI Training

G Com Solutions offer a range of courses on Microsoft Power BI Training Courses, suitable for beginners and advanced users alike at our training centre in Peterborough. We can also organise Power BI on-site training anywhere in the UK.