none
How to extract entire table using power query RRS feed

  • Question

  • Hi all,

    I tried to extract data on a website using power query. The table on the website only shows 50 observations although it actually contains more than 50 observations (e.g. 60). Unfortunately, Power query did not completely extract the whole table (it only took 50 observations). Is there a way I could extract entire table? I know that using VBA, I can extract entire table by writing "WebSelectionType = xlEntirePage". I wonder if i could do the same thing with power query.


    Thanks

    Yudo


    Thursday, March 26, 2015 3:31 AM

Answers

  • Hi Yudo. If the table navigation occurs via javascript (rather than URL navigation), then Power Query will not be able to pull all the data.

    If you can modify the URL to pull the data in chunks, you can write a formula that will generate a set of URLs, pull all the tables, and concatenate them together.

    Ehren

    Monday, March 30, 2015 11:35 PM
    Owner

All replies

  • Hi Yudo,

    could you please share the URL?

    Thanks!


    Imke

    Thursday, March 26, 2015 6:09 AM
    Moderator
  • Hi Imke,

    Here is the URL

    http://referensi.data.kemdikbud.go.id/index11.php?kode=026001&level=3

    You'll see that you can set the number of entries or click "next" on the bottom. However, the URL does not change for 'the next page'.

    I wrote a simple M script to extract tables automatically and just realized I missed 50++ entries.


    (rayon as text) =>
    
    let
        Source = Web.Page(Web.Contents("http://referensi.data.kemdikbud.go.id/index11.php?kode=" & rayon & "&level=3")),
        Data0 = Source{0}[Data],
        ChangedType = Table.TransformColumnTypes(Data0,{{"No.", Int64.Type}, {"NPSN", Int64.Type}, {"Nama Satuan Pendidikan", type text}, {"Alamat", type text}, {"Kelurahan", type text}, {"Status", type text}})
    in
        ChangedType
    

    Thursday, March 26, 2015 1:14 PM
  • Hi Yudo. If the table navigation occurs via javascript (rather than URL navigation), then Power Query will not be able to pull all the data.

    If you can modify the URL to pull the data in chunks, you can write a formula that will generate a set of URLs, pull all the tables, and concatenate them together.

    Ehren

    Monday, March 30, 2015 11:35 PM
    Owner