Forum Discussion

8 Replies

  • sktneer's avatar
    sktneer
    Resolver I
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        sktneer
        Resolver I
        6 years ago

        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's avatar
    Anonymous
    Not applicable
    6 years ago

    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