Thread:Albert Tasmani/@comment-30967537-20190930124511/@comment-30967537-20190930144823

I wrote the script yesterday, based on an existing script, just changed to pick up the future events. // NOTE: // this script does the Guess Best Car differently to the other WTTT scripts because of the dreaded Formula Firewall error // I tried to resolve it but couldn't get it to work // So, the other scripts only guesss if the flag is set and there isn't already a best car, this does it every time!

// // Create data for the Tournaments WTTT History Events and Cars table // // NOTE: This is a combination of qWikiTpWTTThistory and qWikiTpWTTTcurrent. //       It uses the table format from CURRENT with the historical data from HISTORY. //       Changes here should be reflected there and vice versa. // // Uses common function to retrieve WTTT history from the spreadsheet // fExcelWTTThistory returns a table with the following data ... //   _RowID          The row ID to ensure cars are sorted as per the spreadsheet //   Position        Current WTTT entry position, 1-4 //   DateFrom        First date for this entry //   DateTo          Last date for this entry //   Week            Week number (in the year) //   Thread          Link to the weekly (or special event) thread //   Circuit         e.g. Silverstone //   Layout          Track configuration  e.g. Road Course //   TimeOfDay       e.g. Morning //   Car             e.g. Audi TT RS Coupe //   CarInfo         Record with the car details retrieved from the qAllCars table //   Loan            The car can be loaned to the player for the event, any upgrades will be kept for when the car is acquired //   Best            The best car for the event ... there may be none, one or many! // let

constWikiData = "__WikiData", /*   constHdrTemplate = "{{User:QuickNick/H/WTTTcurrent", constRowTemplate = "{{User:QuickNick/R/WTTTcurrent", constFtrTemplate = "{{User:QuickNick/T/WTTTcurrent", constEndTemplate = "}}",

// load the parameters table tblParameters = Table.Buffer(fExcelParameters), // set the template header, row and footer names constHdrTemplate = fSetParameter(tblParameters, "WTTTcurrentTpHdr"), constRowTemplate = fSetParameter(tblParameters, "WTTTcurrentTpRow"), constFtrTemplate = fSetParameter(tblParameters, "WTTTcurrentTpFtr"), // load the number of Cars per Row parameter constCarsPerRow = Value.FromText(fSetParameter(tblParameters, "WTTTcarsPerRow")),

// set the Guess Best Car flags flagGuessBestCar = Text.Upper(fSetParameter(tblParameters, "WTTTguessBestCar")) = "Y", flagGuessBestCarBestPR = Text.Upper(fSetParameter(tblParameters, "WTTTguessBestCarBestPR")) = "Y",

// set flag for highlighting loan cars flagHighlightLoanCars = Text.Upper(fSetParameter(tblParameters, "WTTThistoryHighlightLoanCars")) = "Y",

// funcAddCars = (numCars, bestCars, bestCar, tblCars) => let tblWork = // 02.       Table.SelectColumns( // 01.        Table.AddColumn( // 00.       tblCars // 01.       ,"_strOut", each //   best car? (           // best car set                if [Best] = "Y"                    then "|BC" & Text.From([_numRowEntry]) & "=Y"                else            // guess best car, pick car with max PR, if more than one with max PR check flag                if  flagGuessBestCar                and numCars > 1                and bestCars = 0            //            //    and bestCar = [_RowID]            //--                and ( (flagGuessBestCarBestPR and bestCar[cntBestCars] = 1) or not flagGuessBestCarBestPR )               and bestCar[numBestCar] = [_RowID]            //                    then "|BC" & Text.From([_numRowEntry]) & "=G"                else                    ""            ) //   loan car? & (if flagHighlightLoanCars and [Loan] = "Y" then "|LC" & Text.From([_numRowEntry]) & "=Y" else "") //   car and ALTernate car-name & "|" & [Car] & (if [CarInfo][WikiAltCar] = "" then "" else "|ALT" & Text.From([_numRowEntry]) & "=" & [CarInfo][WikiAltCar]) ) // 02.       ,{"_strOut"}) // done! ,   strOut = Text.Combine(tblWork[_strOut],"") in strOut, //

//-- funcTblRow = (strWikiData) as table => #table( {constWikiData}, { {strWikiData} } ), //--

// funcBestCar = (tblTest) => let // get the maximum PR   maxPR = Table.Max(tblTest, "MaxPR")[MaxPR], // get all cars with the maximum PR   tblBestCars = Table.SelectRows(tblTest, each [MaxPR] = maxPR), // return a record comprising the best car(s) details // which are checked in funcAddCars above recOut = [           // number of cars with max PR             cntBestCars = Table.RowCount(tblBestCars) // the row-id of the first car with the max PR           ,numBestCar = tblBestCars{0}[_RowID] ] in   recOut, //

// load the WTTT history and output the Wiki data tblWiki = // add table header funcTblRow(constHdrTemplate & constEndTemplate) & /* // 99. return only the concatenated Wiki data column Table.SelectColumns( // 21. add concatenated Wiki data column       Table.AddColumn( // 19. group by date-row to split into rows of x cars Table.Group( // 17. sort       Table.Sort( // 14. expand date grouping Table.ExpandTableColumn( // 04. add event ID, could be useful in sorting/distinguishing events with same dates!       Table.AddIndexColumn( // 03. group by dates and race info, insert event header Table.Group( // 02. I need the Max PR in the next Group step       Table.AddColumn( // 00. load WTTT events //       Table.FirstN(        Table.Buffer(fExcelWTTThistory("F"))    // load Future entries //        ,100) // 02.       ,"MaxPR", each [CarInfo][Full.PR]) // 03.        ,{"DateFrom", "DateTo", "Thread", "Circuit", "Layout", "TimeOfDay"}        ,{             {"tblCars", each Table.AddColumn( Table.AddColumn(                             Table.AddIndexColumn( Table.Sort(                             Table.SelectColumns(_, {"Car", "CarInfo", "Loan", "Best", "_RowID"})                              ,{"_RowID"}) ,"_numDateEntry", 1, 1)                             ,"_numDateRow", each Number.IntegerDivide([_numDateEntry] - 1, constCarsPerRow) + 1 ) ,"_numRowEntry", each [_numDateEntry] - ([_numDateRow] - 1) * constCarsPerRow )            }            ,{"numCars", each Table.RowCount(_), type number}            ,{"bestCars", each Table.RowCount(Table.SelectRows(_, each [Best] = "Y")), type number} // //            ,{"bestCar", each Table.Max(_, "MaxPR")[_RowID], type number} //--            ,{"bestCar", each funcBestCar(_) } //         }) // 04.       ,"_EventID", 1, 1) // 14.        ,"tblCars", {"_numDateRow", "_numRowEntry", "_numDateEntry", "Car", "CarInfo", "Loan", "Best", "_RowID"}) // 17.       ,{ {"_EventID", Order.Descending }, "_numDateRow", "_numRowEntry" })

// 19.       ,{"DateFrom", "DateTo", "Thread", "Circuit", "Layout", "TimeOfDay", "_numDateRow", "numCars", "bestCars", "bestCar"} ,{            {"tblCars", each Table.SelectColumns(_, {"_numRowEntry", "Car", "CarInfo", "Loan", "Best", "_RowID"}) } }) // 21a1. add concatenated Wiki data column       ,constWikiData, each // 21b. add the data            constRowTemplate            &             ( // event header row if [_numDateRow] = 1   // event header then "|R=" & Text.From(Number.IntegerDivide([numCars] - 1, constCarsPerRow) + 1 ) //       set circuit and variant, ignore blank and "???" entries & ( if [Circuit] = "" or [Circuit] = "???" then "" else "|C=" & [Circuit] ) & ( if [Layout] = "" or [Layout]  = "???" then "" else "|S=" & [Layout] ) //       set time of day & ( if [TimeOfDay] = "" then "" else "|D=" & [TimeOfDay] ) // use Date.ToText function to format date & "|DF=" & Date.ToText([DateFrom], "dd-MMM-yyyy") & "|DT=" & Date.ToText([DateTo], "dd-MMM-yyyy") // add the weekly (or special event) thread if specified & ( if [Thread] = "" then "" else "|Thread=" & [Thread] ) //           else ""           ) //    add cars            & funcAddCars([numCars], [bestCars], [bestCar], [tblCars]) // 21a2. end the template and set the type as text        & constEndTemplate, type text) // 99. return only the concatenated Wiki data column ,{constWikiData}) // table footer       &        funcTblRow(constFtrTemplate & constEndTemplate) /* // done!

in tblWiki // That generated the output in my message above!