Forum Discussion

n8schicht's avatar
n8schicht
Helper I
7 years ago

automatic parsing website and create table from data?

Hello,

 

Is there a way to parse a website with Power Bi and put the parsed data in a new table?

From this website i need the following informations:

https://www.iln-logistics.de/partner/

 

Depot-Nr.

Depot-Details - i need the phone number

 

Is that possible?

 

 

11 Replies

  • n8schicht go to edit query, add new blank query, click advanced editor and paste the following code.

     

    let
        Source = Web.BrowserContents("https://www.iln-logistics.de/partner/"),
        #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "[data-plz_start=""0""]"}}),
        #"Changed Type" = Table.TransformColumnTypes(#"Extracted Table From Html",{{"Column1", type text}}),
        #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7", "Column1.8", "Column1.9", "Column1.10", "Column1.11", "Column1.12", "Column1.13", "Column1.14", "Column1.15", "Column1.16"}),
        #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", type text}, {"Column1.4", type text}, {"Column1.5", type text}, {"Column1.6", type text}, {"Column1.7", type text}, {"Column1.8", type text}, {"Column1.9", type text}, {"Column1.10", type text}, {"Column1.11", type text}, {"Column1.12", type text}, {"Column1.13", type text}, {"Column1.14", type text}, {"Column1.15", type text}, {"Column1.16", type text}}),
        #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Column1.2", "Column1.3", "Column1.5", "Column1.8", "Column1.10", "Column1.11", "Column1.13", "Column1.14", "Column1.15"}),
        #"Cleaned Text" = Table.TransformColumns(#"Removed Columns",{{"Column1.4", Text.Clean, type text}, {"Column1.6", Text.Clean, type text}, {"Column1.7", Text.Clean, type text}, {"Column1.9", Text.Clean, type text}, {"Column1.12", Text.Clean, type text},  {"Column1.16", Text.Clean, type text}}),
        #"Trimmed Text" = Table.TransformColumns(#"Cleaned Text",{{"Column1.4", Text.Trim, type text}, {"Column1.6", Text.Trim, type text}, {"Column1.7", Text.Trim, type text}, {"Column1.9", Text.Trim, type text}, {"Column1.12", Text.Trim, type text}, {"Column1.16", Text.Trim, type text}}),
        #"Renamed Columns" = Table.RenameColumns(#"Trimmed Text",{{"Column1.1", "Depot-Nr"}, {"Column1.4", "Depot Name"}, {"Column1.6", "Address"}, {"Column1.7", "Ort"}, {"Column1.9", "Telefon/Telefax"}, {"Column1.12", "Email"}, {"Column1.16", "Contact"}})
    in
        #"Renamed Columns"
    • n8schicht's avatar
      n8schicht
      Helper I

      parry2kThank you for your quick reply!

       

      unfortunately I get the following error message:

       

      for Source:

       

      Expression.Error: The name Web.BrowserContents was not recognized. Is he spelled correctly?

      and Extracted Table From Html:

       

      Expression.Error: The import "Html.Table" does not correspond to any export. This may be due to a missing module reference.

      you have an idea? 

      • parry2k's avatar
        parry2k
        Super User

        n8schicht interesting, not sure what is this. Do you have most recent powerbi desktop?