As a newly appointed Datanaut in Clouds on Mars, I work with Power BI on a daily basis. My first short assignment was to help our client with the connection to the API in Power BI. The pilot report was connected to only one web page. However, the requirement of our client was to have a report connected to multiple web pages. The list of webpages was expected to change over time (It was also downloaded from API). Tt meant iterating through the table with IDs and doing a web connection to each URL with a new ID. It was a good opportunity to use M language. There is no loop function in M, so I also had to go around this limitation.

As an example, let us say that I want to download the weather data for multiple cities in Poland. The URL looks like this:

https://danepubliczne.imgw.pl/api/data/synop/station/jeleniagora

I am interested in 7 following cities that I have already put in the table Cities:

City
jeleniagora
warszawa
szczecin
koszalin
lublin
katowice
legnica

To download the data manually, I would have to import the data from a web page 7 times and merge the tables. Feasible, however would be much harder with 100 cities or a dynamic table with cities i.e. table that is also a web query. To make the process of automation clear I have broken it into several short steps:

First step is to download the data for one of the cities and perform the transformations. This should leave us with a table in a satisfactory format. When downloading data for one city, I get this table:

Naturally, to make the data easier to merge with the rest of the cities I have to pivot this table and rename the columns.

Second step comes after making all of the transformations to the data. It is making a custom function that takes a city as a parameter and returns a table similar to the one above.

To do it, I edit the M script in advanced editor:

The script (below) has to have a parameter declared in the beginning and in the URL, so that it can access different webpages in each call.

Before “let” I insert “(city as text) =>”, which is a declaration of a parameter. Later, I replace “jeleniagora” with the parameter using & to connect them. Finished script looks like this:

It is very similar to the script of the initial table. I rename it to get_weather. I can call it by typing in the parameter:

Third step involves calling the function repetedly for each city. I select blank query from sources and type in a script, which looks like this:

City_names gives me a list of all cities I want to iterate through. By using List.Transform(city_names, each get_weather(_)) Power BI applies the function to each city from the list and merges them into a list. The result of the query should look similar to this:

To get the data I transform it to table:

And expand:

This gives me a table with the data for all the cities:

This approach can be used with other connectors as well. Important thing to note is the ability to iterate through a list with List.Transform() function. If you know any other way to perform this action, let me know!

Thanks