Power BI Webinar Part 2 - Transforming and preparing the data

Now that we have loaded all the data we need into Power BI, the next step is to transform this raw data into readable and manageable information.

In this section, we will walk you through how to get your data ready and readable for the report.

1. Group you Queries
2. Combine your Content Libraries
3. Extracting your Data
4. Pivot your Pursuit Smart Fields

The first thing you’ll need to do is select “Transform Data” in the Home ribbon. This will launch a new Power BI window on your desktop.

Group your queries

In the Webinar, you can find this section at 11:20

All the data tables that you’ve imported will be visible in the left menu bar, under “Queries”. We strongly advise that you group your data and maintain good housekeeping with any new queries you create. The idea here is to keep together all the data that comes from Salesforce in the Salesforce group, all QorusDocs data queries in the QorusDocs group, etc.

1. Here you will see all your queries, the groups have already been created here[OH1] [MJ2] :
   1. CRM (Salesforce)
   2. QorusDocs
   3. SharePoint
   4. Other Queries (if any)
2. By right-clicking on a query you can select “Move to Group”
3. If there is no group you need yet you can create a “New Group”

Back to the top

Combine your Content Libraries

In the Webinar, you can find this section at 12:07

Next, we will look at “Appending Queries” which is a query that merges two or more tables together. In this case, we will be merging our SharePoint Lists and Libraries. See below how to achieve this:

1. Click on “Append Queries” in the top ribbon
2. From the dropdown select the second option “Append Queries New”. This will keep the table for the appended items and create a new, appended table.

This will bring up the “Append” window.

1. Select “Three or more tables”
2. Choose the SharePoint Lists and Libraries you’d like to include
3. Either double-click on the item or use the “Add” button to get them to the right pane
4. Click “OK” once you have everything

Once this has loaded you will see an “Append1” table under your Queries. Then right-click on it to bring up the menu to rename it to “All Libraries Combined” and then move it to the SharePoint group.

Back to the top

Extracting your Data

In the Webinar, you can find this section at 13:12

Next, we’ll extract only those records that we will be working with from the combined content library table. Let’s have a look at what this involves.

First off, let's take a look at how we can extract the file titles.

With the table selected in the left menu, scroll across your data table columns until you see the “FieldValuesAsText” column.

• Select the little arrowed box on the top right of the column
• Deselect all and only reselect the records you want. We suggest grabbing
  •  Title
  • ModerationStatus
  • Uniqueid

Repeat the same steps for SME, but just extract the “Name” record this time:

Complete these steps for all the records you’d like to extract. We suggest you focus on users, such as SMEs and Authors, and File names. However, the rest is up to you. Below you will see the fields we’ve expanded in this example in the “Applied Steps”:

The final data to extract would be your managed metadata lookups. This follows the same extraction process with the addition of one extra step. Since Managed Metadata lookups are stored as a list, which you will first need to extract before they can become records. From there, the steps are the same as demonstrated above.

With Managed Metadata lookups, select “Label”:

Finally, we have the “Parent List”. This will give you information such as the name of the List or Library as well as the site the data is found in, to name a few. In our example, we have just extracted the Name of the List or Library and the ParentWeb:

And that’s it for the extraction and retrieval of your raw data.

Back to the top

Pivot your Pursuit Smart Fields

In the Webinar, you can find this section at 21:00

Lastly, we pivot your Pursuit Smart Fields. Let’s run through how to do this.

1. Navigate to the “PurstuitSmartFields” table under the “QorusDocs” Grouping
2. Select the “PursuitID” column
3. Enter the following formula in the top text box: = Table.SelectColumns(PursuitSmartFields_table,{"PursuitId", "FieldDisplayName", "FieldValue"})

This will remove unnecessary columns, leaving you only with the following: the Pursuit ID, Smart Field name, and the Smart Field value.

Next, we’ll transform the data by pivoting it. This will combine all the Smart Field Values with their corresponding Pursuit so that the data is more easily digestible.

1. Have “PursuitID” selected
2. Enter the following formula in the top text: = Table.Pivot(#"Removed Other Columns", List.Distinct(#"Removed Other Columns"[FieldDisplayName]), "FieldDisplayName", "FieldValue")

And there you have it. All your raw data is extracted and transformed, ready for use and interpretation.

Remember to click “Close and Apply” when you’re done:

In Part 3 - Relating the data, we will look into building the relationships between all the tables you have just worked on.

Back to the top