Extract Paginated API data in Power BI using Power Query and M language

hello hello and welcome to the introverted manager in today's video I will show you how to extract data from P paste paginated apis directly inbi without utilizing any additional tools of scripts just for quiry and am language first I will show you briefly how page based apis look like and then do a book through on how to set it up let's Dive Right In okay let's start by looking into to postman and how page based apis look like here I have some API I found on the internet which fits the bill basically if you send a request this is what we get as a response two parameters that are of interest for us page and total pages that what we will be working with today basically what we'll need to do is to extract uh total Pages count and iterate through all of them in this case it's just two it's not much but it will work for us just for the sake of example of course that there could be hundreds of pages it won't change the approach itself and here is what um API provides us with with the data some user information let's move on to the PBI here I have example prepare it already what I like to do first is to create parameter where I will store URL of the API you will see that it will become useful because I will be using that URL in a few places in my M code and therefore in case I decide to change it along the way I would rather store it separately change it in one place and it will Cascade everywhere let's move on to the m code itself and have a look what happens there so this is the m code that we'll be working with uh first I have this function which called get page it gets page number as an input and it processes the uh API response let's go through it it sends the request here is uh our URL that I was talking about and here is the parameter that will it will be itera through the page number then it gets the data from API response and stores it away in temporary variable called data and converts it into a table we start with first page page right therefore we run this function and pass number one for the first page to process initial results once that happens we can extract total Pages this is what happens here as you can see we extract that total page parameter in our case it's just two pages but it might as well be 100 Pages once we extracted total pages we need to generate the list of all the pages that we'll be iterating through this is what happens here it makes a list with all the numbers from one to 100 or in our case to two so just two lines in the list and then what happens for that list of pages we're executing our get page function that that I was talking about earlier basically for each page I execute that function so page one execute get page and it gets the data and response page two get data page three get data page four get data and so on and so on and so on and once that we're done with that we're combining all of the data from all of the pages and returning the table table pretty straightforward out of all of the API offset based cursor based page based page based ones are the simplest simplest ones the most straightforward FS so once we finished here we got the table what I would like to do is to keep all of my transformations of the data separately so I usually make reference uh table and then do all of the transformation there in case I decide to change something about the API itself or the way I work with it not to affect the Transformations that I already made so and once I'm here in reference table I put all of the Transformations here and lower the data inbi and here I have all of the data from this test API loaded already user pictures emails names Etc that's how easy it is working with page based apis in for query out of all of the three types uh of the API ssor based offset based and Page based page based are the simplest ones and most straightforward ones that's all for today make sure to check out other videos about 4bi on my channel last week I talked about working with corser based apis next week I will release another video about working with offset based apis in pobi don't miss it subscribe and see you next [Music] [Music] week

Channel: The Introverted Manager Show

Views: 384

Keywords: Introverted Manager, Power BI, Power Query, M Language, APIs, Data Extraction, Page-Based APIs, Data Analytics, Power Query Tutorial, API Integration, Power BI Tips, Power BI How-To, Data Extraction Techniques, Power BI Tutorials, Paginated API

Id: RA0FBbF3Ttk

Length: 6min 30sec (390 seconds)

Published: Sun Mar 03 2024