Extracting Cursor-based API Data in Power BI Without External Tools | Power Query & M Tutorial

hello hello and welcome to the introverted manager in today's video I will show you how to extract data from csor based paginated apis directly in pobi without utilizing any additional tools of scripts just po query and M language first I will dive in briefly into how csor based APS look like and then do a walk through on how to set it up let's Dive Right In let's start by looking into how corser based apis look like this is Postman what you see on screen this is the tool that I often use to prototype and work with apis before I move on to porbi or some other tools that I working with I use Postman to look into the API structure come up with queries and just in general look into it and quate with the data if you look on the left here is a tip for you that I will be releasing two more videos about offset based apis and how to work with them in pobi and Page based apis and how to work with them in porbi another the topic of today's video so let's focus on forer based apis if you look into the address um bar here I have some API I found for Rick and Morty characters I never watched the show I know nothing about the show but this is the API will be using today and uh if you query the API let me send the request this is the response that you get in Json the difference of course based apis is that they provide the usually next parameter in their respon to the next page with data and usually previous parameter with the previous page with with the data and that's what we will be using today to iterate through all of the pages to get all of the characters as you can see there are 126 characters and 42 Pages this API is actually both page page based API and corser based API so we can kind of use both approaches but specifically today I will be looking into csor based approach in po query and using M language in pobi okay let's switch to powerbi and have a look how I structured my PO query to query no pun intended the Rick and Morty API what I like to do first is to create create parameter where I will store my API URL as you can see here I usually do that not to hard code stuff and be able to change it here quickly and it will Cascade everywhere where I am using it next let's look into [Music] the function itself that is saring our API there is actually multiple functions that are are stored within each other but let's walk through them all so basically first thing that I do is I initiate or have variant function that initiates the whole process this function takes that URL or that parameter that I told you about as a input you can see it here fetch data csor based API URL this is what we are giving in the function as an input and it moves on to the next step in the query here next one is to initiate the state where next URL next even though it's our first is that initial URL that we have here and also initiate the dictionary where we'll be storing our data and gradually adding more data which which will be called accumulated so first we initiate this state then we want to function that will be processing our API responses which is called process page so this function is basically Json document web contents which queres Json result from the API and here is our next URL L which will be changing along the way but currently it's our first page right on the first run it's our first page uh we get the results from the API response we create another temporary um variable which stores the data from the response and the new data that will be coming later on the first run of course there's just one page and no previous data but later on we'll have previous page plus new page previous page plus new page all previous Pages plus new page and on and on and on until we exhaust the pages and we're trying to get next page from the API response if we're looking to post back to postman to the response itself as you can see there are two sections info which has next and previous parameters that we're interested in that's what we're trying to get in that function and also we have results which inside has array with different characters let's move on let's go back to PBI so here I'm trying to get next URL unless it's null or does not exist in that case function puts null instead and therefore we're setting at the end new state which is next url url of the next page and accumulated dat data meaning previous Pages plus new data okay let's move on this is actually the main one the loop that we're going through until we exhaust all of the pages this is the function that is responsible for that Loop this is the it generates the list of basically all the pages that we will have although that list is uh generated gradually because in the beginning we don't know how many pages there is basically it will be going through the API until next parameter has something there once next parameter is now it will stop and proceed to the next function but until there is something in url for next page it will continue running so it starts with the initial State this line will never run again while once we finish the F first Loop the initial Loop and then it moves on to next stages as I mentioned it goes and goes and goes and goes and itates through pages until there is now in next page parameter and it executes that process page function which I showed you earlier which goes through the page extracts next page URL extract results array puts it into accumulated and so it goes and goes and executes this Loop executes this function which gradually populates our array of of results called accumulated once URL is null it moves on to the final data and what it does is basically returns that array of all the results from all the pages that we've gotten that's what we we are getting as the result of this whole po query f whole po query query once we do let me close that that's what you will get at the end list of Records that's what we want what I usually do is that I'm not starting processing it right here right away but rather I'm doing a reference table from this one you can right click on the query and make a reference that's what I did here and then start processing it first convert to table and once you do you can actually extract all of the columns from that Json response or Json array that we populated as you can see there are plenty of columns what I again usually do in this case I don't I unclick use original column name as a prefix because I don't need column one as a prefix less renaming so let's move on to the next step here is our table and from here you can do whatever you want I usually go through all of the columns remove the ones that I don't need or further extract the ones that I want for example location or origin and then remove everything unnecessary because once you close and load those tables into po VI uh your tables will will ever be present in the memory therefore the less data you load after Transformations the better the less memory you use the better the performance so I usually remove all the columns that I do not use and at the end I change timeses okay and if I switch to the 4bi itself I already have it loaded it here uh it's a simple table just for the sake of showing you the data at the end here is the data loaded all of the characters T and tens and hundreds of characters from that uh corser based API that's all for today make sure to check out other videos about pobi on my channel next week I will release another video about working with API and 4 API this time around about page based apis don't miss it subscribe and see you next [Music] week

Channel: The Introverted Manager Show

Views: 571

Keywords: Introverted Manager, Power BI, Power Query, M Language, Data Extraction, API Data, Cursor-Based API, Power BI Tutorial, API Pagination, Power BI Data Management, Power BI for Beginners, Power Query M Code, Data Transformation, Data Analytics, Power BI Advanced Techniques, BI Data Extraction, Power BI Optimization, Power BI API Integration, Postman

Id: wQSK1HFGrvM

Length: 12min 36sec (756 seconds)

Published: Mon Feb 26 2024