Power Query: Blog List – Part 5

Welcome to our Power Query blog.   I would like to get a list of all the Power Query blogs I have posted.  Today, I look at why the method I used required Power Query in Power BI.

I started writing this blog series way back in 2016:

There have been many developments since then, some of which I have reported upon.  Since I am going to revisit more of the areas that have been improved, I am going to start with a reminder of what we have covered so far.  In order to do this, I am going to use Power Query in Power BI, since there are some functions that will help me to access web data, which are not yet recognised in Power Query (Get & Transform) in Excel.

In Part 1, I noted that to get a full list, I am going to need several pages:

I extracted and tidied up the data for the first page:

In Part 2, I changed the Source step of my query to accept a parameter P_PageNumber, which allows me to get the data from a specific page.  I used the following M code:

I checked that this still gave the same results for the query:

In Part 3, I converted this query into a function and generated a list of page numbers to concatenate my data:

Last week, I finished the query by extracting the last page number from the data, and using it as a parameter P_LastPage when generating the list:

The list was then generated, and can be refreshed, no matter how many pages exist.

I can load this query to Power BI, by choosing to ‘Close & Apply’ from the Home tab:

This loads the data into the Data pane, and I can create a Table visual with the data in Extract Blogs:

I can export this to Excel.  I start by clicking on the ‘More options’ icon.

 I can then see the ‘Export data’ option:

I choose a location for the CSV file:

This will export the data, and I can then load it to Excel, but what is it about the queries that means they cannot be run from Excel?  To answer this, I copy the Extract Blogs query:

I can then paste it into the ‘Queries & Connections’ pane in a blank workbook:

This copies most of the queries to my new workbook, but the process is not smooth!

I will look at what this error means in a moment.  Meantime, in the Power Query editor, the only query without errors is the parameter P_PageNumber:

The other queries are using functionality from Power Query in Power BI, which is not available here:

This is the same error that I encountered when pasting the queries.  ‘Web.BrowserContents’ is not recognised.  This is the M function used in the Source step:

The M code is:

Whilst this is recognised in Power BI, Web.BrowserContents() is not used by Power Query in Excel, hence the errors.  There is more M code used in fn_ExtractBlogs, which is also not recognised here:

The helper query Table1 has not been copied, so I need to look in the ’Advanced Editor’ from the Home tab:

Despite the fact that ‘No syntax errors have been detected’, we clearly have some!  The source step is using Web.BrowserContents(), and the next step is using Html.Table():

If I try to view this function in the Formula bar, I get an error:

This is the same error I saw earlier for Web.BrowserContents().  If I try to access the webpage from Power Query in Excel, the Graphical User Interface (GUI) does not provide any useful data:

Until Power Query in Excel is able to use the same functionality as Power Query in Power BI, only webpages that use tables can be accessed in this way by Power Query in Excel.  

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