Forum Discussion

rd3300's avatar
rd3300
New Member
6 years ago

Multiple Pages from UCF website into 1 table

I have no idea I've never heard of Power BI until today. I tried to search through questions but I honestly don't understand the terminology.

 

For work I go to a web-page. It is like 40,000 entries long. I copy a page then paste into excel, click next and repeat. Repeat. It takes hours.

https://ucf.uscourts.gov/search?SelectedCourts=&CreditorSearch=&DebtorSearch=&CaseNumber=&Amount=1000&EnteredOn=

 

This is the website. 

 

I stumbled on a video showing someone import as one table multiple years (that were different and seperate). Can I somehow get all of these pages as One table? That would save me HOURS.

 

ANd I get this maybe a total basic question. I am sorry.

Or if someone could explain I'd appreciate so I want all of the entries over $1000 on the same spreadsheet or whatever. 

 

If this is super simple I'm sorry.

 

-Rob

Need Help

3 Replies

  • rd3300's avatar
    rd3300
    New Member
    6 years ago

    If someone can just do it for me as I am getting more confused seeing like googlesheets make similar claims and excel.

    THat is turn all those pages into one spreadsheet (prefarably in numerical order) I would throw some cash their way (not a lot) I don't have much but you'd really be helping me out (I have a kid if that helps).

     

    • v-piga-msft's avatar
      v-piga-msft
      Icon for Resident Rockstar rankResident Rockstar
      6 years ago

      Hi rd3300 ,

      Normally, we could get data from web page via web connector in power bi desktop.

      For your scenario, we cannot get all the data with the web connector at onetime. By my test, we could only get the page 1 data in power bi with the web connector, I'm afraid that should be casued by the web API design.

      I have no other good idea for this.

      Hope MFelix  and parry2k  can give further advice.

      Best  Regards,

      Cherry

  • Gregory_Felton's avatar
    Gregory_Felton
    Icon for Helper I rankHelper I
    6 years ago

    I have attached a file here that does what you want...but I have also included the code below:

     

    Function that is called to get the tables from each page:

     

    (page as number) as table =>

    let
    Source = Web.BrowserContents("https://ucf.uscourts.gov/search?Page=" & Number.ToText(page) & "&SelectedCourts=&CreditorSearch=&DebtorSearch=&CaseNumber=&Amount=1000&EnteredOn="),
    #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE.table.table-striped > * > TR > :nth-child(1)"}, {"Column2", "TABLE.table.table-striped > * > TR > :nth-child(2)"}, {"Column3", "TABLE.table.table-striped > * > TR > :nth-child(3)"}, {"Column4", "TABLE.table.table-striped > * > TR > :nth-child(4)"}, {"Column5", "TABLE.table.table-striped > * > TR > :nth-child(5)"}, {"Column6", "TABLE.table.table-striped > * > TR > :nth-child(6)"}}, [RowSelector="TABLE.table.table-striped > * > TR"]),
    #"Promoted Headers" = Table.PromoteHeaders(#"Extracted Table From Html", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"", type text}, {"Court", type text}, {"Case", type text}, {"Creditor Name", type text}, {"Debtor Name", type text}, {"Amount", Currency.Type}})
    in
    #"Changed Type"

    -------------------------------------------------------------------------------------

    Query that calls the above function and makes a few transformations:

     

    let
    Source = if #"Page Limit Number" < 2 then 2 else #"Page Limit Number",
    Custom1 = {1..Source},
    #"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Page Num"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Page Num", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "GetDataFromPages", each GetDataFromPages([Page Num])),
    #"Expanded GetDataFromPages" = Table.ExpandTableColumn(#"Added Custom", "GetDataFromPages", {"", "Court", "Case", "Creditor Name", "Debtor Name", "Amount"}, {"Column1", "Court", "Case", "Creditor Name", "Debtor Name", "Amount"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded GetDataFromPages",{{"Debtor Name", type text}, {"Creditor Name", type text}, {"Case", type text}, {"Court", type text}, {"Column1", type text}, {"Amount", Currency.Type}})
    in
    #"Changed Type1"

    -------------------------------------------------------------------------------------------

    Parameter so that a person can dynamically select the amount of pages they want:

     

    2 meta [IsParameterQuery=true, Type="Number", IsParameterQueryRequired=true]