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

All tables pages with same URL, how to deal in power query?

Each site has different structure and methods.

You need to use your browser, and the developer tool in it, to study what requests are sent to the web service and what is returned.
Identify the transaction that contains info you are after. Then you need to decipher how it is structured.
Then plan your attack accordingly.

For this site, info is contained in first <script> tag in the <body> as json object.
 
Chihiro said:
Each site has different structure and methods.

You need to use your browser, and the developer tool in it, to study what requests are sent to the web service and what is returned.
Identify the transaction that contains info you are after. Then you need to decipher how it is structured.
Then plan your attack accordingly.

For this site, info is contained in first <script> tag in the <body> as json object.
Click to expand...
Thanks for reply, im new in this scenario...i tried to open in power query json option, but didnt work...With TextfromBinary its work, but i dont know how to read this script contained in first <script> tag in the <body> in power query. Im stucked in this part.
Thanks
 
It's simple matter of text manipulation using Text.Split().

Once you have extracted out Json string part, use Json.Document to parse. However, you will need to do further exploring of data, as the data contains multiple lists/records that are used to link numeric ID with string value.

Code: 
let
    Source = Text.FromBinary(Web.Contents("https://www.fotmob.com/leagues/130/stats/season/16168/players/rating/")),
    temp1 = Json.Document(Text.Split(Text.Split(Source, "window.__PRELOADED_STATE__ = "){1},"        window.__USE_TRACKJS__ = "){0})
in
    temp1

Result of above code:
75223

I'd recommend using above as starting point. Then use reference/copy of this query to split out table/lists into separate query. Then load to data model and create/define relationships. Then use Pivot Table to report on data.
 
Chihiro said:
It's simple matter of text manipulation using Text.Split().

Once you have extracted out Json string part, use Json.Document to parse. However, you will need to do further exploring of data, as the data contains multiple lists/records that are used to link numeric ID with string value.

Code: 
let
    Source = Text.FromBinary(Web.Contents("https://www.fotmob.com/leagues/130/stats/season/16168/players/rating/")),
    temp1 = Json.Document(Text.Split(Text.Split(Source, "window.__PRELOADED_STATE__ = "){1},"        window.__USE_TRACKJS__ = "){0})
in
    temp1

Result of above code:
View attachment 75223

I'd recommend using above as starting point. Then use reference/copy of this query to split out table/lists into separate query. Then load to data model and create/define relationships. Then use Pivot Table to report on data.
Click to expand...

The field that i want is trending(list)...but when i extracted it to separate, there is no data...its empty, so i cant manipulate the data. i tried to use ExtraValues.List() for that...
Thanks for helping
 
Are you sure that "trending" is the info you want?
The URL you supplied is for player stats.

At any rate, meat of data is in leagueSeasonStats[statsData]
 
Chihiro said:
Are you sure that "trending" is the info you want?
The URL you supplied is for player stats.

At any rate, meat of data is in leagueSeasonStats[statsData]
Click to expand...

Sorry Chihiro!!! you are right, i did it, it worked....Thanks for your patience
 
You must log in or register to reply here.
Back
Top