218.486.5095 |  920 O’Donnell St Hawley, MN 56549

Implementation Specialists

Microsoft Gold ERP Partner in Minnesota

Dynamics 365

Power BI: Data Modeling

by

Data modeling is an important part of creating Power BI reports as it determines how the data will appear on the report. With data modeling you can select the type, format, category and summarization of data each field contains. Taking the time to model your data will give your reports a more polished, professional look.

To model your data, go to the modeling tab on the top toolbar.

To update a particular field, select from the field list on the right-hand side of the Power BI desktop or select a column from the data table view and then you will be able to select the modeling options. If you have any measures, they will only be available within the field list.

Data Types

Within data types define whether the data column is a number or text. Numerical data types include decimal numbers, whole numbers or date and time.

Format

Further define your data by selecting the format. With numerical data you can select currency type, choose from multiple date/time formats, specify how many decimal places to show or format as a percentage or scientific number.

Data Category

Categorizing data is used mainly when you want to have map visuals on your report. To enable Power BI to pull data onto a map it is required to have a column categorized as a location. Locations can either be address fields, such as city/state/postal code or latitude/longitude fields.

Default Summarization

Summarization determines how the data will calculate when it is pulled onto the report. Visualizations can be changed on the report to dictate whether the field should be a sum, average or count, but if a data field is going to be used multiple times on a report it saves time to select a default summarization versus changing each individual visualization.

Now that we’ve covered the basics of data modeling take this knowledge and test it out on your data to determine how it will affect your reports.

-Jerica Coleman, CRM and Power BI Consultant

Power BI: What are people using it for?

by

In the past few posts we’ve talked about how to connect to data and create basic reports, but a big question on a lot of people’s minds is why. Why would I take the time or invest in someone else to go through the process of connecting to data and creating reports?

Most people find it easier to tweak a process or document versus creating one from scratch and we find the same thing with software systems. Looking at how others already use the system allows you to visualize what you would change and how you could use the system within your organization.

Here are some ways customers are currently using Power BI along with a few example reports:

Sales Reports

  • Track year/quarter/month/period to date progress versus previous progress. Are you on target to exceed or fall short?
  • View sales by territory, location, salesperson and month. Focus efforts in higher sales markets and identify weaker areas for improvement.
  • Show top x number of customers or the top x items you are selling. Identify frequent or larger customers and which items are the biggest sellers.

Job Cost Reports

  • Track job P&L at a divisional level, with the ability to drill-down into locations, project managers and individual jobs. Are certain areas or people performing poorly?
  • Ability to search for individual customers or jobs to quickly see how a job is progressing.

Service Reports

  • View total service calls by division, area, month, type. Identify trends in where and why customers are contacting you.
  • Track KPI’s such as average days to complete, average days to invoice, total calls completed. Customer satisfaction lies within service, are you meeting or exceeding set KPI’s?

HR Reports

  • Track attrition by department, manager, job type, tenure, reason for leaving. Identify trends in where and why attrition is happening.
  • Monitor employee reviews due dates and completion. Employee satisfaction drives attrition, keep managers accountable for completing reviews in a timely manner.

There are hundreds of other reports that customers are using, but these are report types we see consistently used across multiple companies. Hopefully, this will give you some ideas on ways you could incorporate Power BI reports into your organization.

Interested in learning more about Power BI? Contact us!

-Jerica Coleman, CRM and Power BI Consultant

Power BI: Coming Clean

by

Once connected to data most people go right into trying to build their visualizations; however, they get frustrated because the data can be overwhelming and sometimes not even make sense.

Cleaning up your data can seem insignificant, but it is the most important thing you will do with your Power BI report. It serves three main purposes:

  1. Makes creating visualizations easier because you don’t have to filter through unnecessary data.

Raw tables from SQL or software systems can include anywhere from 50 to over 300 columns. A lot of these columns may be unnecessary for the reports you are looking to create.

  1. Simplifies end user experience.

Raw tables come over with schema names which may not make sense to end users. It is important to change the names of columns to match the verbiage used within your organization, so that when an end user searches or looks at data they understand what they are looking at.

  1. Improves report performance.

Pulling over 300+ columns and all the rows of data you have entered over the life of your system can really slow down report loading. Cleaning up your data by removing unnecessary columns and filtering down rows helps to speed up the loading when refreshing or updating your data.

Cleaning up data is done within the Power Query Editor which can be accessed two different ways.

When you first connect to your tables you can choose edit your table versus load it.

From the home tab, in the toolbar select Edit Queries.

The Power Query Editor has a lot of different features, in this post we are going to cover three of the features to help clean up most of your data.

Removing Unnecessary Columns

Columns can be removed one at a time or in bulk. To remove one column, either right click on the column itself and choose remove or with the column highlighted select remove columns from the home tab on the toolbar.

To remove multiple columns, you can use CTRL + Select to highlight multiple columns at once and remove as described above, or you can choose which columns to keep by selecting choose columns from the home tab on the toolbar. This will pop up a box that will allow you to uncheck the columns you want to remove.

Renaming Columns

This is a simple step, but so important to make sure your data reflects verbiage used in your organization. Columns can be renamed by either right clicking on the column and selecting rename or by selecting rename from the transform tab on the toolbar with the column highlighted.

Transform Column Data

Clean, consistent data is important to make sure your charts provide beneficial data. You can transform your data either by right clicking on the column and choosing transform or the options are available under the transform tab on the toolbar. There are different transformations available depending on the data type of the column.

Text Transformations:

Change the text within the cell to all lowercase, all uppercase or capitalize each word.

Trim or clean the text to remove extra spaces or non-printable text.

Length calculates the number of characters within a cell.

Date/Time Transformations:

Change to date or time only.

Pull out year, quarter, month, week or day data. This can be the actual time period or it can calculate the start or end of the time period.

For time you can pull out the hour, minute or second.

Number Transformations:

There are several number transformations available, such as rounding, absolute value, base-10, powers or standard mathematics.

If you make a mistake you can undo your editing under the applied steps section on the right hand side of the screen. To delete click the X next to the step. If you have chosen select columns you can click on the settings gear to the right of the step and it will bring the box back up allowing you to reselect the columns you want included.

Once you have completed all the edits close and apply the updates to your Power BI report.

These are just a few of the many functions available within the Power Query Editor, but they will make a big impact when using Power BI. We will dive into some of the other function in more details in future blog posts.

-Jerica Coleman, CRM and Power BI Consultant

Interested in learning more? Contact us!

CRM, Is It a Right Fit For Me?

by

CRM systems are an integrated system used for managing customer relationships. CRM systems can be used throughout your company to improve and track your customers, as well as track how they interact with your company – all in one place.

There are many benefits of using CRM systems, the following are just a few:

  • Data is stored in one location.
  • Communication and collaboration across your company is improved.
  • Managers are able to more efficiently monitor their teams and spot potential problems earlier.
  • Historical data and forecasting ability creates an environment for sustainable growth.

Are you curious about how to make CRM work for you and your company? Join our CRM/BI Consultant, Jerica, for one of our upcoming CRM Webinars. If you can’t attend, still register and we will send you a copy of the recording.

January 30th – Managing your data through CRM dashboards

February 20th – CRM: Global and Advanced Finds

April 24th – Why CRM is a great Asset to Your Company

Have questions about CRM? Contact us for more information.