A real loop function in Power BI
Today, I will go through how to create a loop function using List.Generate() and a Custom Function. I will show how to loop through pages in an API call. This would normally be solved using a for loop in e.g. Python.
There is no function in Power Query that resembles For loop, so I had to look for an alternative. After doing a thorough research I learned that some developers use List.Generate() to create lists with numbers. This is later used in List.Transform(), which calls a function for each number. Pretty similar to a loop, isn’t it.
As an example, I will be using API with information about beer recipies. I know that it has 10 pages. The page number could’ve also been a result of another API call.
|
1 |
https://api.punkapi.com/v2/beers |
Getting the data from the API is fairly easy:
|
1 2 3 4 5 6 |
let Source = Json.Document(Web.Contents("https://api.punkapi.com/v2/beers")), Table = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), Expanded = Table.ExpandRecordColumn(Table, "Column1", {"id", "name", "tagline", "first_brewed", "description", "image_url", "abv", "ibu", "target_fg", "target_og", "ebc", "srm", "ph", "attenuation_level", "volume", "boil_volume", "method", "ingredients", "food_pairing", "brewers_tips", "contributed_by"}, {"Column1.id", "Column1.name", "Column1.tagline", "Column1.first_brewed", "Column1.description", "Column1.image_url", "Column1.abv", "Column1.ibu", "Column1.target_fg", "Column1.target_og", "Column1.ebc", "Column1.srm", "Column1.ph", "Column1.attenuation_level", "Column1.volume", "Column1.boil_volume", "Column1.method", "Column1.ingredients", "Column1.food_pairing", "Column1.brewers_tips", "Column1.contributed_by"}) in Expanded |
But how would I tackle the problem of pages? First, I have to create a custom function. It will return a table with information about recipies for a specific page. I just edit the code above:
|
1 2 3 4 5 6 7 8 9 |
(page as number) => let Source = Json.Document(Web.Contents("https://api.punkapi.com/v2/beers?page="&Text.From(page))), Table = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), Expanded = Table.ExpandRecordColumn(Table, "Column1", {"id", "name", "tagline", "first_brewed", "description", "image_url", "abv", "ibu", "target_fg", "target_og", "ebc", "srm", "ph", "attenuation_level", "volume", "boil_volume", "method", "ingredients", "food_pairing", "brewers_tips", "contributed_by"}, {"Column1.id", "Column1.name", "Column1.tagline", "Column1.first_brewed", "Column1.description", "Column1.image_url", "Column1.abv", "Column1.ibu", "Column1.target_fg", "Column1.target_og", "Column1.ebc", "Column1.srm", "Column1.ph", "Column1.attenuation_level", "Column1.volume", "Column1.boil_volume", "Column1.method", "Column1.ingredients", "Column1.food_pairing", "Column1.brewers_tips", "Column1.contributed_by"}) in Expanded |
Now that it is ready, I can create a list with numbers from 1 to 10, a call the function for each.
|
1 2 3 4 5 6 7 8 9 |
let Pages = 10, Numbers = List.Generate(()=>1, each _ <= Pages, each _ +1), Beers= List.Transform(Numbers, each Bring_me_some_beer(_)), Table = Table.FromList(Beers, Splitter.SplitByNothing(), null, null, ExtraValues.Error), Expanded = Table.ExpandTableColumn(Table, "Column1", {"Column1.id", "Column1.name", "Column1.tagline", "Column1.first_brewed", "Column1.description", "Column1.image_url", "Column1.abv", "Column1.ibu", "Column1.target_fg", "Column1.target_og", "Column1.ebc", "Column1.srm", "Column1.ph", "Column1.attenuation_level", "Column1.volume", "Column1.boil_volume", "Column1.method", "Column1.ingredients", "Column1.food_pairing", "Column1.brewers_tips", "Column1.contributed_by"}, {"Column1.id", "Column1.name", "Column1.tagline", "Column1.first_brewed", "Column1.description", "Column1.image_url", "Column1.abv", "Column1.ibu", "Column1.target_fg", "Column1.target_og", "Column1.ebc", "Column1.srm", "Column1.ph", "Column1.attenuation_level", "Column1.volume", "Column1.boil_volume", "Column1.method", "Column1.ingredients", "Column1.food_pairing", "Column1.brewers_tips", "Column1.contributed_by"}) in Expanded |
I could have used List.Numbers(1,10) instead and it would have worked fine too. Let me know what you think!
Thanks
Hi michal, i seem to get an error “Too many requests” when i try this example. is there another example i could try this looping with?
thnanks