Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
chonchar
Helper V
Helper V

New table based on iterative API calls

Hello! I have imported a table that is based on a CSV file. It is called table 1. In table 1, there is an "ID" column that needs to be referenced in an API call. Table 1 is below: 

chonchar_0-1720617743165.png

 

I am able to create Table 2 based on this m code:

 

= Json.Document(Web.Contents("https:api_url_all_the_stuff", [Headers=[Accept="application/json", #"x-api-key"="API_Password_and_key_stuff", #"x-table1-id"="1001"]]))

 

The problem is that this only refers to a single "ID" from table 1 and there are hundreds of entries for "ID" in table 1. 

 

I need table 2 to look like this: 

 

chonchar_1-1720618067218.png

 

I would like to make a new table with a single query that iterates each "ID" from table 1 and puts all of the information from the api call in table 2. 

 

Please advise! Kudos and Accepted Solutions are sure to follow. 

 

@foodd @parry2k @amitchandak @Greg_Deckler @MFelix @rajendraongole1 

 

1 ACCEPTED SOLUTION
parry2k
Super User
Super User

@chonchar here are quick steps:

 

- click advanced editor of table 2 and get the copy of the M code

- start new blank query, click advanced editor and type

(id as text) =>
and after this paste the M code 

 

Save and close and you have M function with Is as parameter



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

10 REPLIES 10
parry2k
Super User
Super User

@chonchar Glad to help - well done 👍 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

parry2k
Super User
Super User

@chonchar can you paste the m code (not screen shot) you are using to add a new column in table1, also can you paste the code for the new function you created (remove any sensitive API key) before sharing? 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2k I figured it out through your direction. I am going to mark another reply as the solution. Thank you for your help. Again. 

parry2k
Super User
Super User

@chonchar  Check my original reply

 

once it is done, add new column in table 1 and call function (created in step 1) with id as parameter. You will get a table returned from the function and the last step is to expand this new column. This will do it.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2k I am so close. So, I have this:

chonchar_0-1720622197467.png

But once I open that table, it is only creating the ID from the original M. 

 

chonchar_1-1720622248848.png

 

What ID Value goes here, or the column name?

chonchar_1-1720622762462.png

@parry2k Does this look correct?

 

parry2k
Super User
Super User

@chonchar here are quick steps:

 

- click advanced editor of table 2 and get the copy of the M code

- start new blank query, click advanced editor and type

(id as text) =>
and after this paste the M code 

 

Save and close and you have M function with Is as parameter



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

chonchar_0-1720620990546.png

 

@parry2k I got to here, but I don't understand how to reference the column? This still only has on ID in the m code.

parry2k
Super User
Super User

@chonchar create a m function with id as parameter and the use table2 code in this function, replace id with the parameter name.

 

once it is done, add new column in table 1 and call function (created in step 1) with id as parameter. You will get a table returned from the function and the last step is to expand this new column. This will do it.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2k I am unfamiliar with how to create a parameter with M. I am looking into documentation now. 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.