{"id":374,"date":"2018-06-13T08:00:44","date_gmt":"2018-06-13T06:00:44","guid":{"rendered":"http:\/\/cwiok.pl\/?p=374"},"modified":"2018-06-12T20:12:59","modified_gmt":"2018-06-12T18:12:59","slug":"a-real-loop-function-in-power-bi","status":"publish","type":"post","link":"https:\/\/cwiok.pl\/index.php\/en\/2018\/06\/13\/a-real-loop-function-in-power-bi\/","title":{"rendered":"A real loop function in Power BI"},"content":{"rendered":"

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.<\/p>\n

\"\"<\/p>\n

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.<\/p>\n

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.<\/p>\n

https:\/\/api.punkapi.com\/v2\/beers<\/pre>\n
Getting the data from the API is fairly easy:<\/p>\n
let\r\n    Source = Json.Document(Web.Contents(\"https:\/\/api.punkapi.com\/v2\/beers\")),\r\n    Table = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),\r\n   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\"})\r\nin\r\n    Expanded<\/pre>\n
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:<\/p>\n
(page as number) =>\r\n\r\n\r\nlet\r\n    Source = Json.Document(Web.Contents(\"https:\/\/api.punkapi.com\/v2\/beers?page=\"&Text.From(page))),\r\n    Table = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),\r\n   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\"})\r\nin\r\n    Expanded<\/pre>\n
Now that it is ready, I can create a list with numbers from 1 to 10, a call the function for each.<\/p>\n
let\r\n\r\n    Pages = 10,\r\n    Numbers = List.Generate(()=>1, each _ <= Pages, each _ +1),\r\n    Beers= List.Transform(Numbers, each Bring_me_some_beer(_)),\r\n   Table = Table.FromList(Beers, Splitter.SplitByNothing(), null, null, ExtraValues.Error),\r\n   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\"})\r\nin\r\n  Expanded<\/pre>\n
I could have used List.Numbers(1,10) instead and it would have worked fine too. Let me know what you think!<\/p>\n
Thanks<\/p>\n","protected":false},"excerpt":{"rendered":"
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.<\/p>\n
\"\"<\/p>\n
Read More<\/a><\/div>","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[26],"tags":[],"class_list":["post-374","post","type-post","status-publish","format-standard","hentry","category-powerbi"],"_links":{"self":[{"href":"https:\/\/cwiok.pl\/index.php\/wp-json\/wp\/v2\/posts\/374","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/cwiok.pl\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/cwiok.pl\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/cwiok.pl\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/cwiok.pl\/index.php\/wp-json\/wp\/v2\/comments?post=374"}],"version-history":[{"count":0,"href":"https:\/\/cwiok.pl\/index.php\/wp-json\/wp\/v2\/posts\/374\/revisions"}],"wp:attachment":[{"href":"https:\/\/cwiok.pl\/index.php\/wp-json\/wp\/v2\/media?parent=374"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/cwiok.pl\/index.php\/wp-json\/wp\/v2\/categories?post=374"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/cwiok.pl\/index.php\/wp-json\/wp\/v2\/tags?post=374"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}