• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

WebSite single URL has dual table pages, how to select the other page?

Status
Not open for further replies.
While it is possible to do it via Power Query. It will not be easy.

First, you will need to use browser's developer tool to trace the request sent, when you click on "performance", and identify what type of request is used.
In this case, it's POST request, using Json form data.
You will need to parse form data for use in the query.
Code: 
{"filter":[{"left":"description","operation":"nempty"}],"options":{"lang":"en"},"symbols":{"query":{"types":["sector"]},"tickers":[]},"columns":["description","change","change|1W","change|1M","Perf.3M","Perf.6M","Perf.YTD","Perf.Y"],"sort":{"sortBy":"description","sortOrder":"asc"},"range":[0,150]}

Set this as variable in M code (note you'll need to escape double quotes).
Then use Text.ToBinary to create binary record to use in post request.
You will also need to set headers and likely cookie as well.

Have a read of...
Specifying JSON Query in Power Query – Example Statistics Sweden – Erik Svensen – Blog about Power BI, Power Apps, Power Query (wordpress.com)

And do a web search on "Power Query, POST Request, Json form data". You should find more info.
 
Chihiro said:
While it is possible to do it via Power Query. It will not be easy.

First, you will need to use browser's developer tool to trace the request sent, when you click on "performance", and identify what type of request is used.
In this case, it's POST request, using Json form data.
You will need to parse form data for use in the query.
Code: 
{"filter":[{"left":"description","operation":"nempty"}],"options":{"lang":"en"},"symbols":{"query":{"types":["sector"]},"tickers":[]},"columns":["description","change","change|1W","change|1M","Perf.3M","Perf.6M","Perf.YTD","Perf.Y"],"sort":{"sortBy":"description","sortOrder":"asc"},"range":[0,150]}

Set this as variable in M code (note you'll need to escape double quotes).
Then use Text.ToBinary to create binary record to use in post request.
You will also need to set headers and likely cookie as well.

Have a read of...
Specifying JSON Query in Power Query – Example Statistics Sweden – Erik Svensen – Blog about Power BI, Power Apps, Power Query (wordpress.com)

And do a web search on "Power Query, POST Request, Json form data". You should find more info.
Click to expand...

Hi Chihiro, thanks for the reply. I tried reading around on what you have advised but probably I am too amateur to understand. I do not know what does it mean to "Set this as variable M code", "creating binary record or "setting headers and cookies"..... Do you think it will be a bad idea if you are able to show me step by step what to click and type? Really appreciate it.
 
In your browser (using MS Edge as example), navigate to the page.
Using CTRL + SHIFT + I (could be different in another browser), launch developer tool.
Click on "Performance". Use "Network" section in developer tool, and select "XHR".

In Name section, select item one by one, and check what response is returned from it. Identify the one that contains info you want.
In this case, it's "scan".
Go to "Headers" section.

Of particular interest are...
General
Request URL:
Request Method:

Request Headers
accept:
content-type:

Form Data
Make sure you click on view source here.

However, you will need to test things out to troubleshoot. As there are cases where more info from Request headers are needed.
In this case, while cookie is used, it does not seem to be needed (however, there is chance it's using cookie stored on local folder).

Now you just have to fill out the Advanced web query dialog using info obtained from above. It should return json response and you can explore it in the query editor. But since form data isn't sent initially, it returns default and no useful info.

Now go into advanced editor. Set body as variable and use Form data string obtained from developer tool. Note that, all double quotes must be escaped (i.e. using notepad or some other too, replace all instance of " with "").

Then use Text.ToBinary(body) to pass it to Web.Contents as argument.

Sample M query code:
Code: 
let
    body = "{""filter"":[{""left"":""description"",""operation"":""nempty""}],
                ""options"":{""lang"":""en""},""symbols"":{""query"":{""types"":[""sector""]},
                ""tickers"":[]},""columns"":[""description"",""change"",""change|1W"",""change|1M"",
                ""Perf.3M"",""Perf.6M"",""Perf.YTD"",""Perf.Y""],""sort"":{""sortBy"":""description"",
                ""sortOrder"":""asc""},""range"":[0,150]}",
    Source = Json.Document(Web.Contents("https://scanner.tradingview.com/america/scan",
                [Headers=[Accept="text/plain, */*; q=0.01",
                #"Content-Type"="application/x-www-form-urlencoded; charset=UTF-8"],
                Content=Text.ToBinary(body)])),
    data = Source[data],
    #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"s", "d"}, {"Column1.s", "Column1.d"}),
    #"Extracted Values" = Table.TransformColumns(#"Expanded Column1", {"Column1.d", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
    #"Extracted Values"

Result: I didn't fully process data, you can process as required.
74899
 
  • Like
Reactions: nxy
Hi, any idea, why am I getting a token error on this script? Thank you.


Code: 
let
    body = "{""filter"":[{""left"":""index_priority"",""operation"":""nempty""},{""left"":""sector"",""operation"":""equal"",""right"":""index""}],
                ""options"":{""lang"":""en""},""symbols"":{""query"":{""types"":[]},
                ""tickers"":[]},""columns"":[""logoid"",""name"",""change"",""change|1W"",""change|1M"",
                ""Perf.3M"",""Perf.6M"",""Perf.YTD"",""Perf.Y"",""Volatility.D"",""description"",""type"",""subtype"",""update_mode""],
                ""sort"":{""sortBy"":""index_priority"",""sortOrder"":""asc""},""range"":[0,150]}
    Source = Json.Document(Web.Contents("https://scanner.tradingview.com/cfd/scan",
                [Headers=[Accept="text/plain, */*; q=0.01",
                #"Content-Type"="application/x-www-form-urlencoded; charset=UTF-8"],
                Content=Text.ToBinary(body)])),
    data = Source[data],
    #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"s", "d"}, {"Column1.s", "Column1.d"}),
    #"Extracted Values" = Table.TransformColumns(#"Expanded Column1", {"Column1.d", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
    #"Extracted Values"

75041
 
You are missing closing double quote and comma after it in your body ="... portion.

Code: 
    body = "{""filter"":[{""left"":""index_priority"",""operation"":""nempty""},{""left"":""sector"",""operation"":""equal"",""right"":""index""}],
                ""options"":{""lang"":""en""},""symbols"":{""query"":{""types"":[]},
                ""tickers"":[]},""columns"":[""logoid"",""name"",""change"",""change|1W"",""change|1M"",
                ""Perf.3M"",""Perf.6M"",""Perf.YTD"",""Perf.Y"",""Volatility.D"",""description"",""type"",""subtype"",""update_mode""],
                ""sort"":{""sortBy"":""index_priority"",""sortOrder"":""asc""},""range"":[0,150]}",
 
Chihiro said:
You are missing closing double quote and comma after it in your body ="... portion.

Code: 
    body = "{""filter"":[{""left"":""index_priority"",""operation"":""nempty""},{""left"":""sector"",""operation"":""equal"",""right"":""index""}],
                ""options"":{""lang"":""en""},""symbols"":{""query"":{""types"":[]},
                ""tickers"":[]},""columns"":[""logoid"",""name"",""change"",""change|1W"",""change|1M"",
                ""Perf.3M"",""Perf.6M"",""Perf.YTD"",""Perf.Y"",""Volatility.D"",""description"",""type"",""subtype"",""update_mode""],
                ""sort"":{""sortBy"":""index_priority"",""sortOrder"":""asc""},""range"":[0,150]}",
Click to expand...

Thank you!
 
Hi nxy,

Seems like I´m working in something similar of what you´re doing (sort like a dashboard). Did you figured how to separate the data into the other columns? I´m very amateur on this.

Thanks
 
Hi @Chihiro,

Hope you can help me on this please,

I´m trying to do pull the Data Highlights from the EIA website to an excel file (https://www.eia.gov/). I tried what you posted here but I can´t make it work. My goal is to build and energy dashboard in excel with data like this from differente websites, so if I can understand how it works for this I think that I would be able to advance on my task.


Kind Regards78713
 
Status
Not open for further replies.
Back
Top