Data Types Issue

[Mchllwoods]

When I get the Top 250 Movies from the IMDb site I don't get all the right information to bring into Excel and I have to type them in manually. Then when I do bring them into Power Query some rows won't show the cause of the Data Type error or just don't have the information. Next day or so when the list on the site changes my Power Query won't refresh to accommodate it. Also when I enter Directors and Cast member names in Power Query and split them into First, Middle, and Last Name or Suffix as will. the names won't go into the proper column. Link below is the list I'm trying 2 bring to Excel. Please help!

Top 250 Movies

Check out the top 250 movies as rated by IMDb users

www.imdb.com

 

[Anthony47]

Which procedure do you use to import that information?

 

[Mchllwoods]

Which procedure do you use to import that information?

I use power query from web procedure when I split title, rank, etc how I want I then highlight the movie titles and click the movie data types.

 

[Anthony47]

I am sorry, but am not able to support you with PowerQuery
If you wish, you can import that table using web scraping techniques.

For that, you need the "Selenium environment" that should be installed on your computer.
If you are interested, you'll find the instruction on how installing it in this message: extract data from web to inside sheet without open website &refersh

After the installation you could use the following macro:

Sub GetImdb()
Dim WPage As Object
'
'Crea Driver:
Set WPage = CreateObject("Selenium.WebDriver")
myUrl = "https://www.imdb.com/chart/top/"         'Your url
WPage.Start "chrome", myUrl
WPage.Get "/"
WPage.Wait 200
WPage.FindElementsByTag("Table")(1).AsTable.ToExcel Sheets("Sheet1").Range("B2")    '<<< Set a position
WPage.Quit
'
With Sheets("Sheet1").Range("B2").CurrentRegion
    .WrapText = False
    .EntireColumn.AutoFit
End With
End Sub

This will open Chrome, open the IMDB page and import the table in Sheet1-B2

I didn't understand what you do next with the information, but of course the macro can be enhanced to add more action on the imported data

 

[jdellasala]

You MUST Transform. You can't expect it to come in without it. Even if it comes in perfectly (ignoring Data Types), it's always worth first checking.
Also, turn Automatic Type off (Data -> Get Data -> Query Options, under Global -> Data Load, select Never detect column types... This will bite you more often than not, and unless needed Data Types shouldn't be set until the very end, and then just select all columns (Ctrl-A) and use Transform -> Detect Data Types. The big problem is that it hard codes column names which could change, especially when inserting a step.
Here's the code I used:

let
    Source = Web.Page(Web.Contents("https://www.imdb.com/chart/top/")),
    Data0 = Source{0}[Data],
    #"Removed Other Columns" = Table.SelectColumns(Data0,{"Rank & Title", "IMDb Rating"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Other Columns", "Rank & Title", Splitter.SplitTextByEachDelimiter({"."}, QuoteStyle.Csv, false), {"Rank & Title.1", "Rank & Title.2"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Rank & Title.2", Splitter.SplitTextByEachDelimiter({"("}, QuoteStyle.Csv, false), {"Rank & Title.2.1", "Rank & Title.2.2"}),
    #"Replaced Value" = Table.ReplaceValue(#"Split Column by Delimiter1",")","",Replacer.ReplaceText,{"Rank & Title.2.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Replaced Value",{{"Rank & Title.1", "Rank"}, {"Rank & Title.2.1", "Title"}, {"Rank & Title.2.2", "Released"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Rank", Int64.Type}, {"Title", type text}, {"Released", Int64.Type}, {"IMDb Rating", type number}})
in
    #"Changed Type"

That resulted in this in PQ:

and this table

Book1
	A	B	C	D
1	Rank	Title	Released	IMDb Rating
2	1	The Shawshank Redemption	1994	9.2
3	2	The Godfather	1972	9.2
4	3	The Dark Knight	2008	9
5	4	The Godfather Part II	1974	9
6	5	12 Angry Men	1957	9
7	6	Schindler's List	1993	8.9
IMDBTop250

No Data Type problems!