Thanks Mynda for this video! You opened a new way for me to connect and work on interesting data from web! And yes, it was in that unique Mynda style which we have come to love! 😊👍
Great!. I have uploaded my sample. Thank you for your help. under "To summarizes data of Sku select one sku, will display unit weight, change qty of the item say 1,2,3or4 ..unit this will give the total weight of the item(s). | General Excel Questions & Answers | Excel Forum"
@@MyOnlineTrainingHub wow amazing! Is it a female communication thing? LOL... Most guys don't teach with the clarity and highlighting of each action like you do. 😆 So nice! Thank you.
This was helpful for a basic unprotected source request. Do you have any videos on how to access an API that requires a key and credentials (not Windows but a different company's credentials that they provide)?
Great to hear. Examples that require a key and credentials beyond the example given for Stripe which covers authentication? Not sure what this might entail. Please post your question and further details on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Hi, great video! I was wondering if you know how to incrementally load the Data from an API. I have a currency feed and I would like it to keep the previous date's values as well as input the current date's values.
Hard to answer here as it would depend on the API, but presumably you can put in a date range and use the DateTime.LocalNow() function to extract today's date.
thanks im trying it, and it seems to only have the options when the connection is available, otherwise you can also find the authentication settings still I noticed its also asking how much of the packet to apply the password to on one prompt in the "data"banner next to from web data source settings drop down goto data source settings then global settings and then edit permissions button to see those hidden features
thank you for your useful video! i am wondering, does anyone here perhaps know how to deal with APIs, which require their header to be sha256 encrypted, using user data as keys?
How do you handle bearer tokens being required by the API? I have an API that requires a POST call that returns a temporary code used in other GET calls so there is a sequence to it...
I really appreciate the video, Mynda, because at least I suspect that using Power Query to download data from TH-cam Analytics API is possible. Unfortunately, using this video's instructions I wasn't able to actually do it. It seems there are some steps I'm just not understanding. I'm not an engineer or analyst, and although I'm smart, this stuff leaves me feeling stupid. I would gladly pay for coaching or a video specifically on how to do this with the TH-cam Analytics API. I've spent over an hour looking for that online today, and can't find one that helps me get it done.
Thank you for the video… my office internal website data am trying to obtain but unable to get the out put. We use authentication code once we login to web browsers to access complete day. Later the site asks for only user info and we don’t need to provide any password…. Just continue to the site
Thanks for this, I was struggling to find the option to remove the json element name as column prefix b/c PBI was automatically doing the expansion for me, and I saw from this that there is a checkbox for that on the 2nd step.
This is cool. Not sure when I’d use it really but cool! Have you discussed how to create cubes? I’m pretty new using excel to pull data with analysis services but I noticed the existing cubes I’m pulling from don’t always contain the fields I need. I’d love to learn how to create cubes. Probably way over my head. Also, is there a way to specify “current” date periods when importing data from analysis services to excel in order to avoid continuing updates to date parameter filters? Like instead of specifying “year 2020” I’d like “current year”. Sorry this is off topic but you seem to know everything! 🤗
Thank you! I'm not familiar with Analysis Services, sorry. I know you can use Power Query to connect to Analysis Services and SQL Server Analysis Services. With Power Query you can use parameters to filter the data from Analysis Services to only that of the current year. This should enable you to get the data you want, which can then be loaded to Power Pivot which will enable you to query Power Pivot data model using CUBE functions. Hope that points you in the right direction.
Hi Mynda, this is a video I've needed to watch. APIs have been a bit of a "Voodoo" topic for me, but it is something I need to learn, thanks for the video. Is this topic on your newsletter?
Hi I'm trying to retrieve data from Companies House into excel but the API key provided doesn't isn't a URI, it's a string that looks like like this: d4e7d319-2034-47af-b8a0-cdc9ec2e410c. I can't get excel to understand this. Any ideas? Many thanks - Cuala
I have to say that this is the answer to a question I didn't know how to ask. It's info I needed but didn't know it's technical name. Now I need to reverse the process and display the garnered data on a web page. Is this part of your remit or can anyone reccomend some further resources?
Glad it was helpful, Gary! I'm not a web developer, so can't help you with your next step, sorry. That said, if you're wanting to display an Excel workbook in a web page, then you can use the webapp. Here's an example: www.myonlinetraininghub.com/interactive-excel-web-app-dashboard
Thanks! But I have problems when I try to follow your lesson with Excel for Mac. Could you please add some info for people that have this OS? Thanks a lot!
Great tutorial,❤️ Can you help with posting a post request rest apis or with query params with authentication mostly basic LDAP authentication ?. Thanks in advance 👍
@@MyOnlineTrainingHub Sorry for the term, I don't know if their name is "the distribution formulas" or not. I saw one of my colleagues using "beta.dist" to distribute the man-hours over months. Then I realized there are more distribution formulas in the excel, like "beta.dist", "binom.dist" etc.
Thanks for sharing this information, I have a question, I have a api link, there are all customer details, I wanted to get the selected customer from entire data, selected customer are not fix so I will add those selected customer Id in Excel column, and I need the details for these customers only, how can I do this
My pleasure, Amol! In regards to your question, you can probably use a technique similar to getting data from multiple pages as explained here: th-cam.com/video/VkDpwSGWPmk/w-d-xo.html
3:20 Hi, I had a query set up nicely then excel asked again how do i want to connect and by mistake i did not select anonymously this time and selected some other option, now excel keeps throwing errors and i can't find out how to change this setting to anonymous again. please help if you know how to solve this
At the Access Web content dialog box you can choose Basic and enter the credentials. There's more on this process in the step by step written blog in the section about Stripe: www.myonlinetraininghub.com/getting-started-with-apis-in-power-query
Very Useful!! Pls. provide me help on the below Query related to editing Multiple XML Files through excel and saving the the updated data in the Multiple XML files 1.First I imported multiple xml files in excel with mapping the certain fields which I need to edit. 2. Then I edit the data on the mapped fields. 3. I am having problem in updating the edited data into individual multiple xml files Can you help me on this????
Hi Folks, Need some help. How can I use "or" command in API data extraction. I can easily use "&" to filter from multiple columns (for eg: select rows with Value "Canada" in Country_column, & value "Hockey" in Sports_column: api/reporting/request?Country=Canada&Sport=Hockey). But what syntax will I use for selecting two values from the same column. For eg: if I want all data where value is USA Canada in Country Column?? (I tried " ; " , "or" and parenthesis, nothing worked!!) I have to do this as my API fails with the large volume of data, so i can fetch in small sizes.
Hi Joe, unless the API allows you to specify 2 countries (e.g. ?Country1=Canada&Country2=USA) then you can't do this in a single request. You'd have to make 2 requests and then append the results of both. Why is your API request failing for large volumes of data? The API shouldn't fail. Perhaps you need to implement paging to work through all the data returned by the API?
Hi, I am getting data from an API that has two parameters like below in the URL: Param1930=2021-01-01&Param1931=2030-12-31&format=JSON These are the start and end dates of the report that I have manfully set up in the API URL. But, how do I make it dynamic so that everyday that I refresh the data in Power BI connect to the API and pull the latest data? My current M code is something like below: let Source = Json.Document(File.Contents("C:\Users\Ali\AppData\Local\Microsoft\Windows\INetCache\IE\EHD7MHFN\1120")), #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"txtProvider", "txtIncidentNo", "FltDate", "LogTime")
You can use the DateTime. LocalNow() function in place of hard keyed dates to return the current time. From there you can strip out the time element and just keep the date if that's all you need.
Hi Miguel, just because I didn't want any of the links I'd already clicked previously when writing this tutorial to appear purple and distract from the lesson. Mynda
@@MyOnlineTrainingHub No worries , thanks for replying , i am trying to consume a rest api which requires a json payload to be submitted using power query but some how its not working for post requests. Let me know if its really possible. btw nice tutorial 👍 keep Posting
I like the way you explain. You great knowledge about. I need you help. To summarizes data of Sku select one sku, will display unit weight, change qty of the item say 1,2,3or4 ..unit this will give the total weight of the item(s). Which is rounded to number. I have origin zip code to Destination zip to get zone. Once total weight (max 150 lbs/68 kgs)is figured out and zone then it needs to go and check row for corresponding and corresponding column different service type (Priority/Standard/2Day/Ground).and display on first page. I have one example of heading and sku with I need to pull from other tabs and display in first page. SKU:12545470 / Description:E Product item / Unit Shipping Weight (lbs):1.6 / how many max150 lbs/68 kgs:1 / total weight max150 lbs/68 kgs:1.6 / Total Weight Round off (=CEILING(J#,1)):2 Origin Zip :98101 /Destination Zip :12508 / Destination State:NY / Zone:8 . Priority Tab Cost:37.51 / Standard Tab Cost:34.17 / 2Day Cost:17.14 / Ground Cost:8.70 Please advise you are able to teach me how I can do this. I will really appreciate your help. My email address is rakavon@yahoo.ca or rakavon@gmail.com. I can email you the file.
I have question plz Can API querry get the data from each publication at the web source, just by refresh and without consulting the web? Other side your channel it s unblievable !!!!
Glad you're enjoying my videos. An API is a web call, but Power Query doesn't need a web browser open for you to refresh the connection, if that's what you mean.
![🎤 ไปกันต่อออ! จับฉลากร้องเพลง x P’ #XXSIVK [EP.2] (1/2) เพลงของใครจึ้งสุดน้าา 💓🎤 #MXFRUIT](http://i.ytimg.com/vi/mLI_K0sAS4o/mqdefault.jpg)
Very impressive. Thank you for simplifying API connections without all the code and questions about which programming languages etc
Glad you liked it 😊
Ppl make sure to watch all the ads to the fullest because this channel deserves it!
:-) thanks so much!
This was so clearly explained, so easy to understand. It really helped me at work, thank you very much for creating this video.
Awesome to hear!
Thanks Mynda for this video! You opened a new way for me to connect and work on interesting data from web! And yes, it was in that unique Mynda style which we have come to love! 😊👍
Thanks, Vijay! Glad it was helpful :-)
Love you, for saving my job😂❤
Glad I can help!
Thank you Mynda. As always, very interesting information. Your videos provoke new thoughts and ideas.
Pleased to hear that, Tetiana :-)
Excellent, thank you! I was using Python to hit an API and storing the parsed json in a SQL Server table but this might save me some steps!
Great to hear you can make use of it, Mike!
Interesting and unknown topic for me. Thank you very much for providing information about this issue Mynda.
Hopefully you can use it someday, Ivan :-)
Great!. I have uploaded my sample. Thank you for your help. under "To summarizes data of Sku select one sku, will display unit weight, change qty of the item say 1,2,3or4 ..unit this will give the total weight of the item(s). | General Excel Questions & Answers | Excel Forum"
Great!
Clearly and pleasantly presented as always. Thanks :)
So nice of you :-)
@@MyOnlineTrainingHub wow amazing! Is it a female communication thing? LOL... Most guys don't teach with the clarity and highlighting of each action like you do. 😆 So nice! Thank you.
Very helpful.Thank you for sharing your knowledge
My pleasure!
This was helpful for a basic unprotected source request. Do you have any videos on how to access an API that requires a key and credentials (not Windows but a different company's credentials that they provide)?
Great to hear. Examples that require a key and credentials beyond the example given for Stripe which covers authentication? Not sure what this might entail. Please post your question and further details on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Hi Mynda. Super interesting topic. Lots to study and learn. Thanks for sharing :)) Thumbs up!!
Thank you so much, Wayne 🤗
Loved this tutorial Munda, thank you. I will experiment with this...
Cheers, Fred!
thanks
You're welcome!
Hi, great video! I was wondering if you know how to incrementally load the Data from an API. I have a currency feed and I would like it to keep the previous date's values as well as input the current date's values.
Hard to answer here as it would depend on the API, but presumably you can put in a date range and use the DateTime.LocalNow() function to extract today's date.
Informative steps, thanks!
Glad it was helpful, Doug!
thanks im trying it, and it seems to only have the options when the connection is available, otherwise you can also find the authentication settings still I noticed
its also asking how much of the packet to apply the password to on one prompt
in the "data"banner next to from web
data source settings drop down
goto data source settings
then global settings
and then edit permissions button
to see those hidden features
-
Hi Mynda!Great Tutorial,Really Helpful Info...Thank You :)
Cheers, Darryl!
Thanks
You’re welcome 😊
thank you for your useful video!
i am wondering, does anyone here perhaps know how to deal with APIs, which require their header to be sha256 encrypted, using user data as keys?
Hi Mynda.. could you please guide in how to connect jira with power using rest api... that would be of great help
I'm not familiar with Jira, sorry.
How do you handle bearer tokens being required by the API? I have an API that requires a POST call that returns a temporary code used in other GET calls so there is a sequence to it...
Not sure, sorry.
Thanks in a million. Great content. Awesome. Grade: A++💥
Thanks so much 🙏
Excellent , thank you.
Glad you liked it!
I really appreciate the video, Mynda, because at least I suspect that using Power Query to download data from TH-cam Analytics API is possible. Unfortunately, using this video's instructions I wasn't able to actually do it. It seems there are some steps I'm just not understanding. I'm not an engineer or analyst, and although I'm smart, this stuff leaves me feeling stupid. I would gladly pay for coaching or a video specifically on how to do this with the TH-cam Analytics API. I've spent over an hour looking for that online today, and can't find one that helps me get it done.
Hi Micheal, I don't have any experience with the TH-cam API either, sorry.
Very interesting Mynda!
Cheers, Chris!
Thank you for the video… my office internal website data am trying to obtain but unable to get the out put. We use authentication code once we login to web browsers to access complete day. Later the site asks for only user info and we don’t need to provide any password…. Just continue to the site
I suggest you reach out to your IT people for help with using their API.
Can pls tell how to create trans query api structure
Thanks for this, I was struggling to find the option to remove the json element name as column prefix b/c PBI was automatically doing the expansion for me, and I saw from this that there is a checkbox for that on the 2nd step.
Glad it was helpful!
Well done!
Thanks, Daniel!
Thanks, can we do the same for the software like dhis2 ver 2.37
Yes, if it has an API.
Hi
I have a question
I want to login with a username and password and a client ID and secret via API?
Is this also possible?
If there's an API, then it should support validating credentials.
Excellent tutorial. Is it possible to change the style of the table exported by PQ? I am actually annoyed with the green stripes
Thanks, Praveen! Yes, you can change the table styles on the Tables tab of the ribbon.
Well done
Cheers, Drew!
This is cool. Not sure when I’d use it really but cool! Have you discussed how to create cubes? I’m pretty new using excel to pull data with analysis services but I noticed the existing cubes I’m pulling from don’t always contain the fields I need. I’d love to learn how to create cubes. Probably way over my head. Also, is there a way to specify “current” date periods when importing data from analysis services to excel in order to avoid continuing updates to date parameter filters? Like instead of specifying “year 2020” I’d like “current year”. Sorry this is off topic but you seem to know everything! 🤗
Thank you! I'm not familiar with Analysis Services, sorry. I know you can use Power Query to connect to Analysis Services and SQL Server Analysis Services. With Power Query you can use parameters to filter the data from Analysis Services to only that of the current year. This should enable you to get the data you want, which can then be loaded to Power Pivot which will enable you to query Power Pivot data model using CUBE functions. Hope that points you in the right direction.
Hi Mynda, this is a video I've needed to watch. APIs have been a bit of a "Voodoo" topic for me, but it is something I need to learn, thanks for the video. Is this topic on your newsletter?
Glad it was helpful, Dave! Yes, the link in the video description will take you to the newsletter version.
Hi
I'm trying to retrieve data from Companies House into excel but the API key provided doesn't isn't a URI, it's a string that looks like like this: d4e7d319-2034-47af-b8a0-cdc9ec2e410c. I can't get excel to understand this.
Any ideas?
Many thanks
- Cuala
No idea, sorry. You'll need to go back to the API developers/documentation.
thank you! so much!
My pleasure 😊
I have to say that this is the answer to a question I didn't know how to ask. It's info I needed but didn't know it's technical name. Now I need to reverse the process and display the garnered data on a web page. Is this part of your remit or can anyone reccomend some further resources?
Glad it was helpful, Gary! I'm not a web developer, so can't help you with your next step, sorry. That said, if you're wanting to display an Excel workbook in a web page, then you can use the webapp. Here's an example: www.myonlinetraininghub.com/interactive-excel-web-app-dashboard
Great Job
Many Thanks
My question is: how I can get an API for any website?
You can only get an API where the website/company has provided one. They must build the API and then share it with you.
Hi thanks for your help
How can reach to site witch need login ??
I tried , but after past the link to get data its fail to access data
You'll have to refer to the API documentation. They're all different, so this isn't something I can help with, sorry.
@@MyOnlineTrainingHub thanks you so much.
Thanks! But I have problems when I try to follow your lesson with Excel for Mac. Could you please add some info for people that have this OS? Thanks a lot!
Hi Cristiano, unfortunately Mac doesn't have the full Power Query functionality yet, sorry.
Great tutorial,❤️
Can you help with posting a post request rest apis or with query params with authentication mostly basic LDAP authentication ?. Thanks in advance 👍
Glad you liked it. I'm not an API expert, sorry.
Thank you for the tutorials, can you explain the distribution formulas also?
Thanks, Paladin! Not sure what you mean by 'distribution formulas', sorry.
@@MyOnlineTrainingHub Sorry for the term, I don't know if their name is "the distribution formulas" or not. I saw one of my colleagues using "beta.dist" to distribute the man-hours over months. Then I realized there are more distribution formulas in the excel, like "beta.dist", "binom.dist" etc.
Ah, thanks for clarifying. I haven't used those functions, sorry!
Thanks for sharing this information,
I have a question, I have a api link, there are all customer details, I wanted to get the selected customer from entire data, selected customer are not fix so I will add those selected customer Id in Excel column, and I need the details for these customers only, how can I do this
My pleasure, Amol! In regards to your question, you can probably use a technique similar to getting data from multiple pages as explained here: th-cam.com/video/VkDpwSGWPmk/w-d-xo.html
3:20 Hi, I had a query set up nicely then excel asked again how do i want to connect and by mistake i did not select anonymously this time and selected some other option, now excel keeps throwing errors and i can't find out how to change this setting to anonymous again. please help if you know how to solve this
Go to the Data tab > Data Source Settings > Edit Permissions > under 'Credentials' select 'Edit.
Thank you so much. This helps immensely ❤
Hello, how would this work if i don't have a API URL, but just a client ID and secret?
I don’t know that it would be possible.
Use Power Query to call API that requires Auth & share excel file to non-technical colleagues
How to do this
At the Access Web content dialog box you can choose Basic and enter the credentials. There's more on this process in the step by step written blog in the section about Stripe: www.myonlinetraininghub.com/getting-started-with-apis-in-power-query
Very Useful!! Pls. provide me help on the below
Query related to editing Multiple XML Files through excel and saving the the updated data in the Multiple XML files
1.First I imported multiple xml files in excel with mapping the certain fields which I need to edit.
2. Then I edit the data on the mapped fields.
3. I am having problem in updating the edited data into individual multiple xml files
Can you help me on this????
Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
thank you
You're welcome, Taha!
How to get data from amazon website?
👍👍👍 very useful
Glad you think so!
Starts at 2:58
Hi Folks, Need some help.
How can I use "or" command in API data extraction. I can easily use "&" to filter from multiple columns (for eg: select rows with Value "Canada" in Country_column, & value "Hockey" in Sports_column: api/reporting/request?Country=Canada&Sport=Hockey). But what syntax will I use for selecting two values from the same column. For eg: if I want all data where value is USA Canada in Country Column?? (I tried " ; " , "or" and parenthesis, nothing worked!!)
I have to do this as my API fails with the large volume of data, so i can fetch in small sizes.
Hi Joe, unless the API allows you to specify 2 countries (e.g. ?Country1=Canada&Country2=USA) then you can't do this in a single request. You'd have to make 2 requests and then append the results of both. Why is your API request failing for large volumes of data? The API shouldn't fail. Perhaps you need to implement paging to work through all the data returned by the API?
How to get data from web which has id and password?
Hi,
I am getting data from an API that has two parameters like below in the URL:
Param1930=2021-01-01&Param1931=2030-12-31&format=JSON
These are the start and end dates of the report that I have manfully set up in the API URL. But, how do I make it dynamic so that everyday that I refresh the data in Power BI connect to the API and pull the latest data? My current M code is something like below:
let
Source = Json.Document(File.Contents("C:\Users\Ali\AppData\Local\Microsoft\Windows\INetCache\IE\EHD7MHFN\1120")),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"txtProvider",
"txtIncidentNo",
"FltDate",
"LogTime")
You can use the DateTime. LocalNow() function in place of hard keyed dates to return the current time. From there you can strip out the time element and just keep the date if that's all you need.
@@MyOnlineTrainingHub Thanks for your help!
Super
Glad you liked it!
Is there a reason why you use incognito windows? Thanks
Hi Miguel, just because I didn't want any of the links I'd already clicked previously when writing this tutorial to appear purple and distract from the lesson. Mynda
how to make a post request with json payload is there any example ?
Not yet, sorry.
@@MyOnlineTrainingHub No worries , thanks for replying , i am trying to consume a rest api which requires a json payload to be submitted using power query but some how its not working for post requests. Let me know if its really possible. btw nice tutorial 👍 keep Posting
Большое спасибо!
Пожалуйста :-)
Hello ma'am my power query not show PDF import options
PDF import is only available in Microsoft 365 versions of Excel.
Will this work with Excel 10
Yes, you can download the Power Query add-in here: www.microsoft.com/en-au/download/details.aspx?id=39379
I am trying to understand why use python when you can use excel...
I like the way you explain. You great knowledge about. I need you help. To summarizes data of Sku select one sku, will display unit weight, change qty of the item say 1,2,3or4 ..unit this will give the total weight of the item(s). Which is rounded to number. I have origin zip code to Destination zip to get zone. Once total weight (max 150 lbs/68 kgs)is figured out and zone then it needs to go and check row for corresponding and corresponding column different service type (Priority/Standard/2Day/Ground).and display on first page.
I have one example of heading and sku with I need to pull from other tabs and display in first page.
SKU:12545470 / Description:E Product item / Unit Shipping Weight (lbs):1.6 / how many max150 lbs/68 kgs:1 / total weight max150 lbs/68 kgs:1.6 / Total Weight Round off (=CEILING(J#,1)):2
Origin Zip
:98101 /Destination Zip
:12508 / Destination State:NY / Zone:8 . Priority Tab Cost:37.51 / Standard Tab Cost:34.17 / 2Day Cost:17.14 / Ground Cost:8.70
Please advise you are able to teach me how I can do this.
I will really appreciate your help. My email address is rakavon@yahoo.ca or rakavon@gmail.com. I can email you the file.
Thanks, Rakesh. Please post your question on our Excel forum where you can also upload a sample Excel file: www.myonlinetraininghub.com/excel-forum
I have question plz
Can API querry get the data from each publication at the web source, just by refresh and without consulting the web?
Other side your channel it s unblievable !!!!
Glad you're enjoying my videos. An API is a web call, but Power Query doesn't need a web browser open for you to refresh the connection, if that's what you mean.
Very informative video. Thank you.
Glad it was helpful!