cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
proscons
Helper III
Helper III

get table form excel URL

Hello.

I have an excel file that includes a list of URL.

 

<Sample list in Excel file>  

https://en.wikipedia.org/wiki/Whole_Foods_Market

https://en.wikipedia.org/wiki/Walmart

...and more.

 

 

I would like to get a specific table (following picture ) in all URL.

This is just one table but would like to see all data in one table.

 

pic.png

Is it possible?

8 REPLIES 8
v-shex-msft
Community Support
Community Support

HI @proscons,

If your records structure not dynamic changes every times, it is possible to coding some M query codes to analytic and extract the fields you wanted from these records.

Can you please share some dummy data with the raw data structure that you received(remove sensitive data) and the expected result to test?  It should help us to do the test on transform/extract your records as a common query table format.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
sktneer
Resolver I
Resolver I

You may create a custom function called fxWebData using the following query...

(url) =>
let
    Source = Web.Page(Web.Contents(url))[Data]{0}
in
    Source

 

And then convert your data with URL lists into an Excel Table and rename it URLs and then create a blank query with the following M Query.

let
    Source = Excel.CurrentWorkbook(){[Name="URLs"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"URL", type text}}),
    ExtractingCompanyFromURL = Table.AddColumn(#"Changed Type", "Company", each Text.AfterDelimiter([URL], "/", {0, RelativePosition.FromEnd}), type text),
    InvokingCustomFunction = Table.AddColumn(ExtractingCompanyFromURL, "Data", each fxWebData([URL])),
    RemovedTheURLColumn = Table.RemoveColumns(InvokingCustomFunction,{"URL"}),
    FinalData = Table.ExpandTableColumn(RemovedTheURLColumn, "Data", {"Column1", "Column2"}, {"Column1", "Column2"})
in
    FinalData

 

For details, refer to the attached.

You may get data privacy warning first time and you will need to click on Continue and choose Public from both the comboboxes.

 

Download Excel File

 

Regards,
Subodh Kumar Tiwari (sktneer)

 

 

 

 

@sktneer 

Thank you for your advice!

I just changed with my real URL and done a same thing.

 

my column show's like this😥

paint.png

Sorry for asking you again and Thank you😊

 

That doesn't look like a URL.

Why not share the file and let me know which site you want to fetch the data from based on the string which you call as URLs?

How do these strings construct a web address?

@sktneer 

 

Thank you for your reply😉

 

Link is not in English so I am not sure you can understand.

 

Thank you!

 

You may delete the file as I downloaded it.

 

You are right, since it's not in English I am unable to understand the parsed data but the query seems to be working as intended.

@sktneer 

Thank you for your help.

So, if this is correct, do you mean there is nothing I can do with this URL to pick a data automatically ? 

The query is already picking the correct data as per the URLs and the query steps but somehow it returns the weird output and I obviously don't know the reason behind it.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.