I need to download xml data from multiple xml url strings every day, into Excel.
there are approx 300 url strings.
Only the data component of each string (2017/5/6) changes every day, otherwise, every string is constant. The date component of each url string will always be tomorrows date. (tomorrow meaning the day after i am gathering the data)
Most of the url strings will not be active (return empty or non/active) on a given day, but there will always be between 20 and 180 that do contain data, So i just need to check all and the ones that do contain data can be transferred to Excel.
i can format the data after the transfer if necessary, or perhaps PQ can achieve it, but the main goal i am looking for is automation with the process of the changing the dates within the url.
I have been told that Power Query can fully automate the task of changing the date component of each xml URL string every day.
I have been looking at Power Query (its not simple) and i have a few broad questions i was hoping to answer before i continue this uphill journey.
1. Can PQ automate this task, or at least semi-automate it?
2. Can PQ achieve a certain format within excel from the xml data that is gathered. For example can the data be neatly set out in a table-like structure for easy uploading into a database?
3. Is PQ a sensible choice for this task? Is there a better option?
4. If PQ is a good choice for this task, is there a link to a tutorial that specifically covers the xml (or related) part of PQ? I cant seem to find the right information online.
thanks in advance
there are approx 300 url strings.
Only the data component of each string (2017/5/6) changes every day, otherwise, every string is constant. The date component of each url string will always be tomorrows date. (tomorrow meaning the day after i am gathering the data)
Most of the url strings will not be active (return empty or non/active) on a given day, but there will always be between 20 and 180 that do contain data, So i just need to check all and the ones that do contain data can be transferred to Excel.
i can format the data after the transfer if necessary, or perhaps PQ can achieve it, but the main goal i am looking for is automation with the process of the changing the dates within the url.
I have been told that Power Query can fully automate the task of changing the date component of each xml URL string every day.
I have been looking at Power Query (its not simple) and i have a few broad questions i was hoping to answer before i continue this uphill journey.
1. Can PQ automate this task, or at least semi-automate it?
2. Can PQ achieve a certain format within excel from the xml data that is gathered. For example can the data be neatly set out in a table-like structure for easy uploading into a database?
3. Is PQ a sensible choice for this task? Is there a better option?
4. If PQ is a good choice for this task, is there a link to a tutorial that specifically covers the xml (or related) part of PQ? I cant seem to find the right information online.
thanks in advance
