Excel - Microsoft Excel Substitute Function

The Excel Substitute function replaces occurrences of a search text string, within an original text string, with the supplied replacement text. 

The syntax of the function is : 

SUBSTITUTE( text, old_text, new_text, [instance_num] ) 

Where the function arguments are: 

The Excel Substitute function is similar to the Excel Replace Function, but the Substitute function replaces one or more instances of a given text string, while the Replace function replaces text in a specified position of a supplied string. 

Note also, that the Excel Substitute function is case sensitive. Therefore, if the old_text argument is the text string "A", this will NOT replace instances of the lower case text string "a". 

Substitute Function Examples

The spreadsheets below show examples of use of the Excel Substitute Function. The spreadsheet on the left shows the format of the functions and the spreadsheet on the right shows the results. 

Substitute Function Common Problem

Use of the Excel Substitute Function with Numbers, Dates and Times 

The Excel Substitute function is designed for use with text strings and returns a text string. 

Therefore, if you attempt to use the substitute function with a date, time or a number, it will give you unexpected results. 

One solution to this problem is to convert the date, time or number into text, using the Excel Text To Columns tool: 

Excel - Power BI Summary Part 10/10

Over our last 9 blogs we have given you an overview of Power Bi

Power Bi Desktop puts visual analytics at your fingertips with intuitive report authoring. Drag-and-drop to place content exactly where you want it on the flexible and fluid canvas. Quickly discover patterns as you explore a single unified view of linked, interactive visualisations

Course Duration: 1 Day

Course Types: Group Bookings, Public Scheduled Courses, 1-2-1 Sessions, Bespoke Tailored Courses.

Course Costs:

Public Courses : £250 +VAT      Next Scheduled Course Please call for details
Group Rate 1-8: £ 695 + VAT
Group Rate 1-12 :£ 695 + VAT
1-2-1 Rate £ 450 + VAT

View Scheduled Public Courses

Tailored course content can be customised to meet your specific requirements, with scheduled dates to suit you.

Course Location: This Microsoft Power Bi is delivered at our training centres in London Venues; training can also be delivered at your offices. 

Course Outline:

Module 1: Getting Started with Power BI Desktop

Power BI Concepts and Overview

Introduction to Main Features

Imports

Visualizations

Filters and Queries

Reports 

Module 2: Connecting to Data Sources with Power BI Desktop

Data Sources Power BI Desktop will connect to

Data Types and Properties in Power BI Desktop

Import and Enter Data

Get Data and Query Editor 

Introducing the Query Editor

Using the Query Editor to Connect to Data 

Combining Data Sources as a Mash Up 

Working with Relationships in Data 

Module 3: Data Visualizations

Overview of Data Visualizations 

Tables and Matrix Views 

Applying Conditional Formats

Charts 

Interactive Data and Date Slicers 

Drill Visualizations to see underlying data

Create Reusable Field Hierarchies

Record Grouping and Binning in Visualizations 

Drill Visualizations with using Dates 

Clustering 

Analytics and Forecast Lines in Visualizations 

Creating Role Based Views 

Data Categories, Geo-Data and Maps

Module 4: Querying and Shaping the Data

Common Activities using Query Editor 

Filters 

Transforms 

Pivot and Group By 

Creating Custom Calculated Columns

Adding Conditional Columns

Introduction to DAX Expressions 

Histograms

Module 5: Reports and Dashboards

Reports and Output Options 

Report Elements and Options 

Working with Pages 

Adding Graphics 

Visual and Filter Settings 

Export Power BI Data to CSV

Create a Power BI Template

Microsoft Accredited IT Training Provider

Microsoft Certified Training Materials

IT Training Courses delivered by Microsoft Accredited Facilitators

Microsoft MOS Test Centre London

Excel - Power BI - Graphical Examples

The Power Of the

Power BI Interface

Are You Ready? 

Power BI gives you the ability to display data in many ways we have put together some examples for you to show how data can be presented the image above uses two separate tables to collect information from in our first set of examples we only exploit simple filtering and relationship techniques the following pages show data cut in different ways.

In this image you are seeing the use of a slicer to display just the retail section as highlighted in red. 

In this image you are seeing the use of a slicer to display just the Wholesale section as highlighted in red.

In this image you are seeing the use of a slicer to display just the language English section, sales to only the sales for language selected as highlighted in red.

In this instance we are looking at the Japanese market for sales of the flute, if you clear the slicers you can click on the two pie charts to filter the data further, you will get to build all of the above on our courses.

Here we have appended 5 sets of regional data to create a simple pie chart learn how to introduce the county breakdowns and drill down to the underlying data our courses tell you how.

Take a look at your data on a map zoom in to take a closer look at the regions learn this and much more on our Power BI Course.

Power BI can integrate in so many ways here we will set you up to examine your google analytic pages

You can gleam so munch information and present it in so many different ways 

All of the above data charts have not used any Dax Formulas or calculated fields, these take you to the next level and can be learnt on our two day course.

Additionally  you yet you can export your data into PowerPoint. Power BI is a continual evolution and is being updated every month with new upgrades available to the programmed.  

Excel - Power BI - Cleaning Irregularly Formatted Data Part 9/10

While Power BI can import your data from almost any source, its visualization and modeling tools work best with columnar data. Sometimes your data will not be formatted in simple columns, which is often the case with Excel spreadsheets, where a table layout that looks good to the human eye is not necessarily optimal for automated queries. For example, the following spreadsheet has headers that span multiple columns..

Fortunately, Power BI has tools to quickly transform multi-column tables into datasets that you can use.

Transpose data

For example, using Transpose in Query Editor, you can flip data (turn columns to rows, and rows into columns) so you can break data down into formats that you can manipulate.

Once you do that a few times, as described in the video, your table begins to shape into something that Power BI can more easily work with.

Format data

You also may need to format data, so Power BI can properly categorize and identify that data once it's imported.

With a handful of transformations, including promoting rows into headers into to break headers, using Fill to turn null values into the values found above or below in a given column, and Unpivot Columns, you can cleanse that data into a dataset that you can use in Power BI.

With Power BI, you can experiment with these transformations on your data, and determine which types get your data into the columnar format that lets Power BI work with it. And remember, all actions you take are recorded in the Applied Steps section of Query Editor, so if a transformation doesn't work the way you intended, you can simply click the x next to the step, and undo it.

Create visuals

Once your data is in a format that Power BI can use, by transforming and cleansing the data, you can begin to create visuals.

What's Next

Congratulations! You've completed this section of the Guided Learning course for Power BI. You now know how to get data into Power BI Desktop, and how to shape or transform that data, so you can create compelling visuals.

The next step in learning how Power BI works, and how to make it work for you, is to understand what goes into modeling. As you learned, a dataset is a basic building block of Power BI, but some datasets can be complex and based on many different sources of data. And sometimes, you need to add your own special touch (or field) to the dataset you create.

Excel - Power BI - More Advanced Data Sources and Transformation Part 8/10

In this article, we investigate some advanced data import and cleaning techniques for Power BI Desktop. Once you've shaped your data in Query Editor and brought it into Power BI Desktop, you can look at it in a few different ways. There are three views in Power BI Desktop: Report view, Data view, and  view. You see each view by selecting its icon in the upper left side of the canvas. In the following image, Report view is selected. The yellow bar beside the icon indicates which view is active.

To change the view, just select either of other two icons. The yellow bar beside the icon indicates which view is active.

Power BI Desktop can combine data from multiple sources into a single report, at any time during the modelling process. To add additional sources to an existing report, select Edit Queries in the Home ribbon and then select New Source in Query Editor.

There are many different possible data sources you can use in Power BI Desktop, including Folders. By connecting to a folder, you can import data from multiple files at once, such as a series of Excel files of CSV files. The files contained within your selected folder appear in Query Editor as binary content, and clicking the double-arrow icon at the top of the Content column loads their values.

One of Power BI's most useful tools is its Filters. For example, selecting the drop-down arrow next to a column opens a checklist of text filters that you can use to remove values from your model.

You can also merge and append queries, and turn multiple tables (or data from various files, in folders) into a single table that contains just the data you want. You can use the Append Queries tool to add the data from a new table to an existing query. Power BI Desktop attempt to match up the columns in your queries, which you can then adjust as necessary in Query Editor

Next

Excel - Power BI - Clean and Transform Your Data With the Query Editor Part 7/10

Power BI Desktop includes Query Editor, a powerful tool for shaping and transforming data so it's ready for your models and visualizations. When you select Edit from Navigator, Query Editor launches and is populated with the tables or other entities you selected from your data source.

You can also launch Query Editor directly from Power BI Desktop, using the Edit Queries button on the Home ribbon.

Once Query Editor is loaded with data that's ready for you to shape, you see a handful of sections:

1. In the ribbon, many buttons are now active to interact with the data in the query

2. In the left pane, queries (one for each table, or entity) are listed and available for selection,       viewing, and shaping

3. In the center pane, data from the selected query is displayed and available for shaping

4. The Query Settings window appears, listing the query’s properties and applied steps

In the center pane, right-clicking on a column displays a number of different available transformations, such as removing the column from the table, duplicating the column under a new name, and replacing values. From this menu you can also split text columns into multiples by common delimiters.

The Query Editor ribbon contains additional tools, such as changing the data type of columns, adding scientific notation, or extracting elements from dates, such as day of the week.

As you apply transformations, each step appears in the Applied Steps list in the Query Settings pane on the right side of Query Editor. You can use this list to undo or review specific changes, or even change the name of a step. To save your transformations, select Close & Apply on the Home tab.

Once you select Close & Apply, Query Editor applies the query changes you made, and applies them to Power BI Desktop.

There are all sorts of things you can do when transforming data in Query Editor, including advanced transformations. In the next section, we take a look at a few of those advanced transformations, to give you a sense of the almost immeasurable ways you can transform your data with Query Editor.

Next

Excel Power BI - Connect to Data Sources in Power BI Part 6/10

Connect to Data Sources in Power BI Desktop

Power BI Desktop can connect to a whole range of data sources, including on-premises databases, Excel workbooks, and cloud services. Currently, over 59 different cloud services such as GitHub and Marketo have specific connectors, and you can connect to generic sources through XML, CSV, text, and ODBC. Power BI will even scrape tabular data directly from a website URL! But let's start from the beginning, with opening Power BI Desktop and connecting to data.

When you start Power BI Desktop and move past the Start Screen, you can choose Get Data from the ribbon on the Home tab.

There are monthly updates to Power BI Desktop, and with each update, the Power BI Desktop What's New page gets updated with information about the updates, links to the blog, and a download link.

In Power BI Desktop, there are all sorts of different data sources available. Select a source to establish a connection. Depending on your selection, you will be asked to find the source on your computer or network, or be prompted to log in to a service to authenticate your request.

After connecting, the first window you'll see is the Navigator. The Navigator displays the tables or entities of your data source, and clicking on one gives you a preview of its contents. You can then import your selected tables or entities immediately, or select Edit to transform and clean your data before importing.

Once you've selected the tables you'd like to bring into Power BI Desktop, you can choose to load them into Power BI Desktop by selecting the Load button in the bottom right corner of Navigator. There are times, however, where you might want to make changes to those tables before you load them into Power BI Desktop. You might want only a subset of customers, or filter that data for sales that occurred only in a specific country. In those cases, you can select the Edit button and filter or transform that data before bringing it all into Power BI Desktop.

Next

Excel Power BI Getting Started Part 5/10

• Getting Started With Power BI Desktop

In this topic, we take a closer look at how the first two parts of Power BI fit together:

• Create a report in Power BI Desktop

• Publish the report in the Power BI service

We’ll start in Power BI Desktop, and select Get Data. The collection of data sources appears, allowing you to choose a data source. The following image shows selecting a Web page as the source, in the video above, Will selected an Excel workbook.

Regardless of which data source you choose, Power BI connects to that data source, and shows you the data available from that source. The following image is another example, this one is from a Web page that analyzes different states and some interesting retirement statistics.

In Power BI Desktop Report view, you can begin to build reports.

The Report view has five main areas:

1. The ribbon, which displays common tasks associated with reports and visualizations

2. The Report view, or canvas, where visualizations are created and arranged

3. The Pages tab area along the bottom, which lets you select or add a report page

4. The Visualizations pane, where you can change visualizations, customize colors or axes, apply filters, drag fields, and more

5. The Fields pane, where query elements and filters can be dragged onto the Report view, or dragged to the Filters area of the Visualizations pane

The Visualizations and Fields pane can be collapsed by selecting the small arrow along the edge, providing more space in the Report view to build cool visualizations. When modifying visualizations, you'll also see these arrows pointing up or down, which means you can expand or collapse that section, accordingly.

To create a visualization, just drag a field from the Fields list onto the Report view. In this case, let’s drag the State field from RetirementStats, and see what happens.

Look at that... Power BI Desktop automatically created a map-based visualization, because it recognized that the State field contained geolocation data.

Now let’s fast-forward a bit, and after creating a report with a few visualizations, we’re ready to publish this to the Power BI service. On the Home ribbon in Power BI Desktop, select Publish.

You’ll be prompted to sign in to Power BI.

When you've signed in and the publish process is complete, you see the following dialog. You can select the link (below Success!) to be taken to the Power BI service, where you can see the report you just published.

When you sign in to Power BI, you'll see Power BI Desktop file you just published in the service. In the image below, the report created in Power BI Desktop is shown in the Reports section.

In that report, I can choose the  icon to pin that visual to a dashboard. The following image shows the pin icon highlighted with a bright box and arrow.

When I select that, the following dialog appears, letting me pin the visual to an existing dashboard, or to create a new dashboard.

When we pin a couple of visuals from our report, we can see them in the dashboard

There’s a lot more you can do with Power BI, of course, such as sharing the dashboards you create. We'll discuss sharing later on in this course..

Next

Excel Power BI - Getting Data Part 4/10

Getting Data

When you get data, sometimes it's not quite as well-formed, or clean, as you want it to be. So in this section you learn how to get data, how to clean it up (sometimes called cleaning or transforming data), and also learn some advanced tricks that can make your data-getting life easier.

As always in this course, your learning journey follows the same path as the flow of work in Power BI. As such, let's check out Power BI Desktop, where it often begins.

An Overview of Power BI Desktop

Power BI Desktop is a tool to connect to, clean, and visualize your data. With Power BI Desktop, you can connect to data and then model and visualize it in different ways. Most users who are working on Business Intelligence projects will spend the majority of their time using Power BI Desktop.

You can download Power BI Desktop from the web, or from the Power BI service. Just select the down arrow button in the upper right side of Power BI, then select Power BI Desktop.

Power BI Desktop installs as an application on your Windows computer.

So once you download it, you'll install Power BI Desktop and run it like other applications on Windows. The following image shows the Start Screen of Power BI Desktop, which appears when you start the application.

Power BI Desktop connects to a wide variety of data sources, from local on-premises databases to Excel worksheets to cloud services. It helps you clean and format your data to make it more usable, including splitting and renaming columns, changing data types, and working with dates. You can also create relationships between columns so that it's easier to model and analyze your data.

Next

Excel Power BI A Look at the Power BI Service Part 3/10

As we learned, the common flow of work in Power BI is to create a report in Power BI Desktop, publish it to the Power BI service, then share it with others so they can view it in the service or on a mobile app.

Some people begin in the Power BI service, though, so let's take a quick look at the service, and learn about an easy and popular way to quickly create visuals in Power BI: content packs.

A content pack is a collection of pre-configured, ready-made visuals and reports based on specific data sources, such as Salesforce. Using a content pack is like microwaving a TV dinner or ordering a fast-food value meal: with just a few clicks and comments, you're quickly served up a collection of entrees designed to go well together, all presented in a tidy, ready-to-consume package.

So let's take a quick look at content packs, the service, and how it works. We go into more detail about content packs (and the service) in upcoming sections; think of this as a bit of taste-testing to whet your appetite.

Create out-of-the-box dashboards with cloud services

With Power BI, connecting to data is easy. From the Power BI service, you can simply select the Get Data button in the bottom left corner of the home screen.

The canvas (the area in the center of the Power BI service) shows you the available sources of data in the Power BI service. In addition to common data sources such as Excel files, databases, or Azure data, Power BI can connect to software services (also called SaaS providers, or cloud services) such as Salesforce, Facebook, Google Analytics, and a whole assortment of other SaaS services just as easily.

For these software services, the Power BI service provides a collection of ready-made visuals, pre-arranged in dashboards and reports called Content Packs. Content packs get you up and running in Power BI quickly with data from the service that you select. For example, when you use the Salesforce content pack, Power BI connects to your Salesforce account (once you provide your credentials) and then populates a pre-defined collection of visuals and dashboards in Power BI.

Power BI provides content packs for all sorts of services. The following image shows the first screen of services, in alphabetical order, that is displayed when you select Get from the Services box (shown in the previous image). As you can see from the image below, there are many to choose from.

For our purposes, we’ll choose . GitHub is an application for online source control. Once I enter the information and credentials for the GitHub content pack, it begins importing my data.

Once the data is loaded, the pre-defined GitHub content pack dashboard appears.

In addition to the Dashboard, the Report that was generated (as part of the GitHub content pack) to create the dashboard is available too, as is the Dataset (the collection of data pulled from GitHub) that was created during the data import, and used to create the GitHub Report.

       

On the Dashboard, you can click on any of the visuals, and automatically be taken to the Report page from which that visual was created. So when the Top 5 users by pull requests visual is clicked, Power BI opens the Pull Requests page in the Report (the Report page from which that visual was created).

Asking Questions of your data

You can also ask questions of your data, and the Power BI service will create visuals based on your question, in real time. In the following image, you can see Power BI creates a number visual showing the Count of Issues closed, based on what is typed in the Natural Language Query bar.

When you have a visual that you like, you can select the Pin icon, to the right of the Natural Language Query bar, to pin that visual to the dashboard. In this case, the visual is pinned to the GitHub dashboard, since that’s the dashboard currently selected.

Refreshing data in the Power BI service

You can also choose to refresh the dataset for a content pack, or other data you use in Power BI. To set refresh settings, select the ellipses (the three dots) next to a data set, and a menu appears.

Select the Schedule Refresh option from the bottom of that menu. The Settings dialog appears on the canvas, letting you set the refresh settings that meet your needs.

That's enough for our quick look at the Power BI service. There are many more things you can do with the service, which we'll cover later in this course. Remember, too, that there are many different types of data you can connect to, and all sorts of content packs, with more of each coming all the time.

Next