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 splitt...
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

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!
    • Anonymous's avatar
      Anonymous
      Not applicable
      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

      • HVPereira's avatar
        HVPereira
        Frequent Visitor
        6 years ago

        I am sorry but I am new to this and I am using excel and I understand your Query M code, but I really don't understand how to do the steps below, could you support me? Sorry for being a noob in the matter :S 

         

        • 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&")