• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

skip those URLs which generate errors on request|Power Query|Excel Forum|My Online Training Hub

You are here: Home / skip those URLs which generate errors on request|Power Query|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower Queryskip those URLs which generate erro…
sp_PrintTopic sp_TopicIcon
skip those URLs which generate errors on request
Avatar
Chloe Bull

Active Member
Members
Level 0
Forum Posts: 4
Member Since:
June 2, 2022
sp_UserOfflineSmall Offline
1
June 2, 2022 - 11:58 pm
sp_Permalink sp_Print

Hi All,

I need a help with MS Query Data from Web.
I have more URLs from one webpage to process, but some of the URLs might not be existing in the time. In MS Query is all defined and with correct URLs it works perfect, but as I have in the list of URLs not existing ones, whole process of the getting data stops and my query doesnt show any data.
Is it possible to make query to skip wrong datasources and to go to next one and so on?

Thank you in advance,

Ch.

sp_AnswersTopicSeeAnswer See Answer
Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4449
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
June 3, 2022 - 8:14 am
sp_Permalink sp_Print

Hi Chloe,

Welcome to our forum. I presume you're talking about Power Query and not the old Microsoft Query?

If so, you can use this http error handling technique for Power Query.

Mynda

Avatar
Chloe Bull

Active Member
Members
Level 0
Forum Posts: 4
Member Since:
June 2, 2022
sp_UserOfflineSmall Offline
3
June 6, 2022 - 6:23 pm
sp_Permalink sp_Print

Thank you Mynda,

I have studied your link, but the issue is, that I know some links are wrong. I have created table with links based on prefix. Some of the links are the future ones, which will be created as time goes by.

So simply I need, when errorr occures, to skip non existing website (yet) and to scrape data from next ones.

Reason for that is, that I want to create a tool which will automatically check the new URLs on the target http as they are rising up.

Any advice on that?

 

M.

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1518
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
4
June 6, 2022 - 7:43 pm
sp_Permalink sp_Print

Hi Chloe,

Can you please provide some sample data and explain how you know some links are wrong and when/why an error will occur.

Without these things it's nearly impossible to write some code for you to deal with the situation you describe.

Regards

Phil

Avatar
Chloe Bull

Active Member
Members
Level 0
Forum Posts: 4
Member Since:
June 2, 2022
sp_UserOfflineSmall Offline
5
June 6, 2022 - 10:24 pm
sp_Permalink sp_Print

Dear Philip,

1. we need to update prices given by the state authority almost every month in a year into our ERP system.

https://www.health.gov.sk/Clan.....kzp-202204

1b. here you can see the excel file (link) with actual pricelist we need to obtain from their web and here are the data.

https://www.health.gov.sk/Zdro.....202204.xls

2. In MS query I have created table (as function) of two existing and the future links, because the script will be always the same for instance in July will be:

https://www.health.gov.sk/Zdro.....acia/zkzp/202207/Zoznam_ZP_202207.xls

which of course this doesn´t exist yet. But it will be.

3. Therefore In MS query I have created a list of two actual and future links which I need to check on web. As soon the query finds out the wrong address I need to skip those and load the next one which has  data. Then I don´t need to visit website everyday to wait for their next excel link on web.

4. Now it works perfectly with existing websites (already published) and based on filter I separe the older data from the freshest which I use than for import into our ERP system.

5. Maybe there is also another way how to do it, how regularly check the website for most actual data. But problem is that as they publish new data in linked excel sheet, it is in the new URL. But result shuld be to get most actual data (prices) from ministry as soon as I refresh my data in Excel.

Thank you very much for any Idea to this issue.

Ch.

PS: In the attachment you will find the excel with MS Query settings.

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1518
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
6
June 7, 2022 - 7:40 pm
sp_Permalink sp_Print

Hi Chloe,

There's no file attached, you need to click on Start Upload after selecting the file.

But, I think what you are trying to do can be done with a query like this

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"YM", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Excel.Workbook(Web.Contents("https://www.health.gov.sk/Zdroje/Sources/kategorizacia/zkzp/" & [YM] & "/Zoznam_ZP_" & [YM] & ".xls"), null, true)),
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Added Custom", {"Custom"}),
#"Kept Last Rows" = Table.LastN(#"Removed Errors", 1),
Custom = #"Kept Last Rows"{0}[Custom],
Sheet1 = Custom{[Name="Sheet1"]}[Data],
#"Changed Type1" = Table.TransformColumnTypes(Sheet1,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}})
in
#"Changed Type1"

Dates in YM format e.g. 202206 are loaded from an Excel table, and the query tries to load a workbook for all of those dates. 

Any dates in error are removed and the latest, most recent, file is then left.  This data is loaded into Excel - see the attached workbook for an example.

Regards

Phil

sp_AnswersTopicAnswer
Answers Post
Avatar
Chloe Bull

Active Member
Members
Level 0
Forum Posts: 4
Member Since:
June 2, 2022
sp_UserOfflineSmall Offline
7
June 8, 2022 - 11:03 pm
sp_Permalink sp_Print

Dear Philip,

I am overwhelmed! You made my DAY!

Thank you very much. It works perfectly.

Have a nice day,

Ch.

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1518
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
8
June 9, 2022 - 8:05 am
sp_Permalink sp_Print

No worries Chloe.

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Ayal Telem
Guest(s) 9
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 871
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
Jessica Stewart: 204
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Murat Hasanoglu
Brett Dryland
Saeed Aldousari
Bhuwan Devkota
Kathryn Patton
Maria Conatser
Jefferson Granemann
Glen Coulthard
Nikki Fox
Rachele Dickie
Forum Stats:
Groups: 3
Forums: 24
Topics: 6222
Posts: 27292

 

Member Stats:
Guest Posters: 49
Members: 31913
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.