DATA for multiple years

[dsmitha]

Hi,

am trying to import data to PBI using GET DATA - > Web method, issue is web page contains data for multiple years ( 2000 to 2021 ) in different tabs, am able to connect to data for single year ( say 2000 ) but i would like to pull data for the all the years , will it be possible. please guide me

regards,

dsmitha 

[PhilipTreacy · Accepted solution]

Hi @dsmitha 

Download this PBIX file with working solution

Here's the Power Query code

let

    GetWebPage = (Year) => let

        Source = Web.BrowserContents("http://planecrashinfo.com/" & Year & "/" & Year & ".htm"),
        #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE > * > TR > :nth-child(1)"}, {"Column2", "TABLE > * > TR > :nth-child(2)"}, {"Column3", "TABLE > * > TR > :nth-child(3)"}, {"Column4", "TABLE > * > TR > :nth-child(4)"}}, [RowSelector="TABLE > * > TR"]),
        #"Promoted Headers" = Table.PromoteHeaders(#"Extracted Table From Html", [PromoteAllScalars=true]),
        #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Location / Operator", type text}, {"Aircraft Type / Registration", type text}, {"Fatalities", type text}})

    in #"Changed Type",
    
    Years = {2020 .. 2022},
    WebPages = List.Transform(Years, each GetWebPage(Text.From(_))),
    #"Converted to Table" = Table.FromList(WebPages, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandTableColumn(#"Converted to Table", "Column1", {"Date", "Location / Operator", "Aircraft Type / Registration", "Fatalities"}, {"Date", "Location / Operator", "Aircraft Type / Registration", "Fatalities"})
    
    
in
    #"Expanded Column1"

To specify which years you want data for, select the Years step, and change the numbers as highlighted in the formula bar.  In this example I am getting data for years {2020 .. 2022}

Resulting in this table

Power Query may ask you to confirm security settings when you first run the query.  Choose ignore if you have no security issues.

Regards

Phil

[dsmitha]

Hi Philip Treacy,

Thanks a ton !!!!!, i have been struggling for a week with this , appreciate 

regards,

dsmitha

[dsmitha]

Hi Philip Treacy,

thank you for your reply, furnished herewith details which will be helpful to solve the issue

this is the details of web page from where am trying to pull data

for eg: if i select 1921

am able to load data for a single year, is there any chance i can pull together data from all the years

regards,

dsmitha

[PhilipTreacy]

Hi @dsmitha 

What's the website?

I'm not sure what you mean by multiple tabs, please provide an example/image or the website URL for the data.

If you are trying to acces data that is generated by JavaScript then you'll probably have trouble as PBI can't properly interact with JS generated tables/pages.

Regards

Phil