Connecting to data sources and transforming data into a useable format can be complex, even when you are using a powerful tool like Power BI. I know many people that have started a transformation only to give up because they are not too sure what steps to take next.  I found the more examples you see of data transformations the easier it becomes to transform data from complex sources.

Today I want to work through an example with you that you would not come across that often, and step by step we will look at transforming the data.

Data source

The data source we are looking at is https://steemd.com.  This website contains data for users of a blockchain social media site.  This domain can be amended to give details of a particual user by placing the use name at the end of the domain.  For example to get my data the domain is https://steemd.com/@paulag

We want to connect to the highlighted table of data for a number of users, and extract 2 values from that table to be used in further calculations.  The fields we are interested in are Voting power and effective sp

Our output should look like this

Step by Step

Connecting to the first table of data

We will first set up a connection by going to Get Data, Other and then Web.  In the domain we will enter www.steemd.com/@paulag

Once Power BI connects to the website the navigator window will open.  On the left you can see a list of tables that Power BI has found.  By selecting a table, you will see a preview on the right.  Once we have found the correct table and ensure that we have ticked it, we can then select edit.

Transforming our data

In our query editor we now have two columns of data and we only want the fields Name, Voting Power and Effective SP.

 Column 1 of our data contains the attributes and column 2 contains the data.  Under the transform ribbon if we select transpose

The data will move so that column 1 is now row 1 and column 2 is now row 2.

The next step in the transformation is to change the first row to the header row.

By selecting the menu on the table of data we can then select Use First Row as Headers.

We can now select the columns we wish to keep.  Name, Voting Power and Effective SP.  From the home ribbon select remove other columns

What remains is the data for that person that you required.

Getting the subsequent tables of data.

Now that we have the data for the first person, we can duplicate the query and make some quick changes to get the second persons data

To duplicate the query, under the queries pane, right click the existing query and select duplicate query

You can then rename this query to the second profile we will be looking at.  This account is SteemitBC.

This time we will use the advanced query editor to make changes to this query. To do this, on the home ribbon select advanced editor.

This will now show the M code behind the query.

On the first line of code, we are simply going to replace @paulag with @steemitbc and save the query.  The data now is correct for the second profile.

To add the third profile we can do the exact same steps and duplicate the query and make a change to the name in the advanced query editor.

Append the tables of data

We now have 3 tables of data that we need to append into one table.  On the home ribbon the next step is to select Append queries

In the append window we will select append 3 or more tables

Select each of the tables on the left and select add to move them to the right.

The final step is to set the data type for voting power to  Percentage and also change the data type of Effective sp to a Decimal Number

And there we have it.  We have connected to a website to extract required values from one of their tables.  We have successfully transformed the data and created a table that can now be used in further calculations.

The table of data for SteemitBC and Minnowsupport can now be set not to load to model as all the data in contained in the first table.