Forum Discussion
John Smith
Jun 08, 2017Copper Contributor
Daily changing web query and how to handle in power query
I have a web site im trying to pull a table from daily. Here is the link to the site I use table 11. http://www.fangraphs.com/leaders.aspx?pos=all&stats=pit&lg=all&qual=0&type=8&season=2016&month...
- Jun 09, 2017
Wyn, you usually explain much better, I'll try to start. I loaded your file and received query firewall alert for using date query. Modified a bit. Anyway
1) Within the excel is the cell with date which we would like to use as parameter. At any place, the only point we name this cell as Date
https://www.computerhope.com/issues/ch000704.htm
2) After that create the query which returns this date. Stay on above cell and in Ribbon Data->From Table/Range. The only one additional step is convert result to text type.
I converte that query to function, thus finally
let fnDate = () => let // Here we take the content of the cell named "Date" in current workbook Source = Excel.CurrentWorkbook(){[Name="Date"]}[Content] , // Result is returned as the table with one row, convert it to text type ChangeTypeToText = Table.TransformColumnTypes(Source,{{"Column1", type text}}), TakeDataValue = ChangeTypeToText{0}[Column1] in TakeDataValue in fnDate
3) After that starting point in Ribbon Data->From Web, enter site URL, select desirable table from menu and after that edit. Actually remove from text with URL last part with date and add instead date received on previous step.
I changed a bit the query for better parametrization:
let // here we define parameters for our query // all constants except the date returned by fnDate() pUrl = "http://www.fangraphs.com/leaders.aspx", pFirstSeason = "2016", pLastSeason = "2016", pType = "8", pNothing = "0", pDate = fnDate(), // This function reads content of pUrl page with parameters in Query Source = Web.Page(Web.Contents(pUrl, [Query = [ #"pos" = "all", #"stats" = "pit", #"lg" = "all", #"qual" = pNothing, #"type" = pType, #"season" = pFirstSeason, #"month" = pNothing, #"season1" = pLastSeason, #"ind" = pNothing, #"team" = pNothing, #"rost" = pNothing, #"age" = pNothing, #"players" = "p" & pDate ] ] )), // from list of tables select our one by ClassName SelectMasterTable = Table.SelectRows(Source, each ([ClassName] = "rgMasterTable")), // Pickup data from selected table TakeIt = SelectMasterTable{0}[Data], // No need in column with headers NoNeed = Table.RemoveColumns(TakeIt,{"Header"}) in NoNeed
Then Close & Load the query to desirable place in Excel sheet (see Sheet2 in attached file)
Wyn Hopkins
Jun 08, 2017MVP
Hi John
Have you used Power Query?
The attached file utilises that and allows you to put in the parameter
Take a look and let me know if you have questions
Cheers
Wyn
John Smith
Jun 08, 2017Copper Contributor
Wyn Hopkins wrote:Hi John
Have you used Power Query?
The attached file utilises that and allows you to put in the parameter
Take a look and let me know if you have questions
Cheers
Wyn
Wyn,
This is my first go around with power Query. I'm an excel novice. I was able to import the table into excel. Is there a way you could walk me through how you built the file that would be great. Once i see how it is done I can break it down easier. My ultimate goal is to put that on a dashboard sheet to pull information for the different players.
Thanks
John
- Wyn HopkinsJun 09, 2017MVPHi John, I'll aim to pull something together over the weekend, or if anyone else out there wants to help out then event better :)
- SergeiBaklanJun 09, 2017MVP
Wyn, you usually explain much better, I'll try to start. I loaded your file and received query firewall alert for using date query. Modified a bit. Anyway
1) Within the excel is the cell with date which we would like to use as parameter. At any place, the only point we name this cell as Date
https://www.computerhope.com/issues/ch000704.htm
2) After that create the query which returns this date. Stay on above cell and in Ribbon Data->From Table/Range. The only one additional step is convert result to text type.
I converte that query to function, thus finally
let fnDate = () => let // Here we take the content of the cell named "Date" in current workbook Source = Excel.CurrentWorkbook(){[Name="Date"]}[Content] , // Result is returned as the table with one row, convert it to text type ChangeTypeToText = Table.TransformColumnTypes(Source,{{"Column1", type text}}), TakeDataValue = ChangeTypeToText{0}[Column1] in TakeDataValue in fnDate
3) After that starting point in Ribbon Data->From Web, enter site URL, select desirable table from menu and after that edit. Actually remove from text with URL last part with date and add instead date received on previous step.
I changed a bit the query for better parametrization:
let // here we define parameters for our query // all constants except the date returned by fnDate() pUrl = "http://www.fangraphs.com/leaders.aspx", pFirstSeason = "2016", pLastSeason = "2016", pType = "8", pNothing = "0", pDate = fnDate(), // This function reads content of pUrl page with parameters in Query Source = Web.Page(Web.Contents(pUrl, [Query = [ #"pos" = "all", #"stats" = "pit", #"lg" = "all", #"qual" = pNothing, #"type" = pType, #"season" = pFirstSeason, #"month" = pNothing, #"season1" = pLastSeason, #"ind" = pNothing, #"team" = pNothing, #"rost" = pNothing, #"age" = pNothing, #"players" = "p" & pDate ] ] )), // from list of tables select our one by ClassName SelectMasterTable = Table.SelectRows(Source, each ([ClassName] = "rgMasterTable")), // Pickup data from selected table TakeIt = SelectMasterTable{0}[Data], // No need in column with headers NoNeed = Table.RemoveColumns(TakeIt,{"Header"}) in NoNeed
Then Close & Load the query to desirable place in Excel sheet (see Sheet2 in attached file)
- Wyn HopkinsJun 09, 2017MVPHi Sergei, really like your breakdown of the query into it's elements.
I'm not a fan of using custom functions in this scenario ( when grabbing a single cell named parameter) when the steps are quick and easy to do it without one. Especially when sharing with non-experienced folk...
Click on Named Cell
Data > From Table
Remove the automatic Promoted Headers and Change Type steps
Change Type to Text
Right Click in Text and Drill Down
Done
- John SmithJun 09, 2017Copper Contributor
Wyn
Thanks for your help.