Power Query: Get Pasted – Part 2

Welcome to our Power Query blog.  This week, after using an alternative approach to a one-off data grab from the web, I transform the data I have pasted.

Last week, I ventured into Power BI.  The task was to get a list of presenters, languages and times from the recently concluded Excel Virtually Global 2022 event.  This data is not going to change as the event is over.

Having not managed to get the data easily from the Get Data -> Web option:

Since this is not something I am planning to refresh (as stated above, the event has been and gone), I approached this another way.  I deleted the query Table 1, and used ‘Enter Data’ to allow me to paste the data from the website into a new table:

I went to the website and copied the data from the table:

I then pasted the data into the ‘Create Table’ dialog:

The data is all in one column, including the presenter names, languages and times.

Now, I will transform this data.  I click OK, as I am happy with the column header:

Now, I just need to create some defined columns.  Since the data is in groups, I create an index starting from one [1] and use that to define my columns:

The key to this is that the data is in groups of three [3], so I can use the Modulo function in the Standard dropdown for ‘Numeric Column’.  Note that I use it from the Transform tab (this is because the ‘Add Column’ variant adds a column after combining selected columns instead rather than undertaking the transformation on the column itself using a scalar value).

I choose to use the scalar value three [3].

This transforms the Index column:

Now the value corresponds to the type of information, I can create three conditional columns.  I will only show the first in detail since the principle is the same for all three.  I select ‘Conditional Column’ from the ‘Add Column’ tab:

The column Presentation time is populated for those rows where the Index is one [1].

I repeat this for new columns Presenter and Language.

Now I can select my three new columns and right click to ‘Remove Other Columns’:

I can select Presentation time and Presenter and right-click to choose to Fill Down:

Now I can see which rows to keep.

I select Language and filter to ‘Remove Empty’:

This leaves me with the required data, I just need to promote the first column to headers:

Come back next time for more ways to use Power Query!