Forum Discussion

HVPereira's avatar
HVPereira
Frequent Visitor
6 years ago
Solved

Append Data with dynamic source

Hello,


I am fetching data from a web Page with a dynamic Source my target is to load all the source the site has and append it into one table. From trial and error i've created the page and splitted it in several queries, so that if the query is empty it would still work, but it seems it doesnt. Please find my code below:

 

Source = Web.Page(Web.Contents("https://x.com")),
Data0 = Source{0}[Data],
#"Appended Query" = Table.Combine({Data0, #"X - Top 20", #"X - Top 19", #"X - Top 18", #"X - Top 17", #"X - Top 16", #"X - Top 15", #"X - Top 14", #"X - Top 13", #"X - Top 12", #"X - Top 11", #"X - Top 10", #"X - Top 9", #"X - Top 8", #"X - Top 7", #"X - Top 6", #"X - Top 5", #"X - Top 4", #"X - Top 3", #"X - Top 2", #"X - Top 1"}),

 

Please find my error:

An error occurred in the ‘X - Top 13’ query. Expression.Error: There weren't enough elements in the enumeration to complete the operation.
Details:
Table

 

Is there anyway to load the existing source only? and combine only the existing one after?

Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi,

    you could try in this way:

    • Create a parameter TableNumber, type text and set = 1
    • Import table 1 from the web site as query 1
    • In the second step of query 1 ("Extracted Table From Html") substitute all 

    DIV.card--clean:nth-child(1)

    with

    DIV.card--clean:nth-child("&TableNumber&")

     

    so you are parametrizing your query.

    • Then Create Function from this query and call it TableReader

     

    • Create a List of Number from 1 to N
    • Transform to a table
    • Add a column invoking TableReader function using first column as parameter
    • Remove Errors
    • Expand Column selecting column you need

    Enjoy.

     

    Your Final Query M Code must be something like this:

     

    let
    NumberRange = {1..20},
    #"Converted to Table" = Table.FromList(NumberRange, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "TableNumber"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"TableNumber", type text}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "Table", each TableReader([TableNumber])),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Invoked Custom Function", {"Table"}),
    #"Expanded Table" = Table.ExpandTableColumn(#"Removed Errors", "Table", {"KICK OFF", "HOME TEAM", "% CHANCE", "HOME ODDS", "DRAW ODDS", "AWAY ODDS", "% CHANCE_1", "AWAY TEAM", "PREDICTION"}, {"KICK OFF", "HOME TEAM", "% CHANCE", "HOME ODDS", "DRAW ODDS", "AWAY ODDS", "% CHANCE_1", "AWAY TEAM", "PREDICTION"})
    in
    #"Expanded Table"

     

    Regards

    Lorenzo

8 Replies

  • HVPereira's avatar
    HVPereira
    Frequent Visitor
    6 years ago

    I was thinking on using the following code:

     

       try Table.Combine({state, {#"Invoked Function"}})

     

    And try making the "Invoked Function" one by one, however Powerbi does not allow me to use the same variable on every try, is there a way to just append on the same table over and over again?

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi,

       

      the structure of your web page source is not so clear for me, without a couple of examples, but I think that you can try building:

      the function to invoke

      a table with one form of your web page in each row

      add a column with the function using value in the first column as parameter

      Then expand the new column and select the columns you need

       

      For a better sugestion please share some samples

      Regards

      Lorenzo

      • HVPereira's avatar
        HVPereira
        Frequent Visitor
        6 years ago
        Hello

        You are right! Sorry for that, please find the page below

        https://oddslot.com/odds/

        The problem here is that the tables can go from 1 to X and I don't know how to make it dynamic.

        Thanks for the help!