Append Data with dynamic source

[HVPereira]

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?

[HVPereira]

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]

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]

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 · Accepted solution]

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]

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

[HVPereira]

Should the code be like? I am not seeing in your M Code where we are fetching the page itself, Sorry I've been trying, but still not a solution at sight :S most likely because I am not understanding how to correlate two power queries like you did, in excel.

let
Source = Web.Page(Web.Contents("https://x.com")),
DIV.card--clean:nth-child("&TableNumber&"),
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"

[Anonymous]

Hi,

I didn't understand you are using excel. I think that Power BI Desktop is easier.

By the way, here two links about custom functions and parameters in excel power query:

https://exceloffthegrid.com/power-query-custom-functions/

https://www.excelguru.ca/blog/2018/05/30/creating-dynamic-parameters-in-power-query/

Here my .pbix, so you can study M code.

Regards

Lorenzo

[HVPereira]

Actually I found a simpler solution on how to load dynamic data:

Source = Web.Page(Web.Contents("https://x.com")),
#"Appended Query" = Table.Combine({Source[Data]}),

This is a way to build and append a table of tables in the simplest way I could find.