automatic parsing website and create table from data?

[n8schicht]

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?

[parry2k]

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

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

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

[n8schicht]

yes the power BI which is included in office365. 
It is the newest version

[parry2k]

@n8schicht when you click on the first line in edit query what do you see?

[n8schicht]

[parry2k]

@n8schicht here is the link to doc from microsoft, not sure what is going on. can you check for your language if something need to be changed.

[n8schicht]

@parry2k 
which version do you use? 

[n8schicht]

@parry2k 

okay i get it. 

I load the power bi desktop x64 and now it works.

but there is one Problem:

it is only parising: data-plz_start="0"

how can i use this to parse all:

data-plz_start="0"

data-plz_start="1"

and so on ?

thx

[parry2k]

@n8schicht you can write a function and pass values and pull the data, do you know # of data_plz-start?

[parry2k]

@n8schicht here it is