Daily changing web query and how to handle in power query

---

SergeiBaklan wrote:

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)

 

 

 

Sergei,

You really helped me out i was able to figure how to load more columns. I have a stupid question how did you get the table too display on sheet2?  Thanks again for the help.

Thanks

John

 

 

 

---