get table form excel URL

[Anonymous]

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.

Is it possible?

[Anonymous]

HI @Anonymous,

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

[sktneer]

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)

[Anonymous]

@sktneer 

Thank you for your advice!

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

my column show's like this😥

Sorry for asking you again and Thank you😊

[sktneer]

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?

[Anonymous]

@sktneer 

Thank you for your reply😉

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

Thank you!

[sktneer]

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.

[Anonymous]

@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 ? 

[sktneer]

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.