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

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

  • HVPereira's avatar
    HVPereira
    Frequent Visitor
    6 years ago

    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"