Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

DATA for multiple years

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 

Need Help
  • PhilipTreacy's avatar
    PhilipTreacy
    4 years ago

    Hi Anonymous 

     

    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

4 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    4 years ago

    Hi Anonymous 

     

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
    PhilipTreacy
    Super User
    4 years ago

    Hi Anonymous 

     

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Philip Treacy,

       

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

       

      regards,

      dsmitha