User:QuickNick/CarsPQexample

This is the PQ script I use to retrieve the data from the Cars page.

// // load all Cars from the Wiki // retrieve information from the Wiki for each car // let funcCarsFromWiki = as table => //==== let

// retrieve all cars from the Wiki Cars page WikiCars = Table.Buffer(Web.Page(Web.Contents("http://rr3.wikia.com/wiki/Cars")){0}[Data]),

// check for column-heading being (incorrectly) "Manufacture/Model" or (correctly) "Manufacturer/Model" constCarColumn = "Manufacturer/ Model", strCarColumn = if Table.HasColumns(WikiCars, constCarColumn) then constCarColumn else "Manufacture/ Model",

// rename columns first so easier to work with WikiCars01 = Table.RenameColumns(WikiCars,       {             {strCarColumn            ,"Car"}            ,{"Purchase Cost"         ,"Cost"}            ,{"Service Time"          ,"SvcTime"}            ,{"Service Cost (R$)"     ,"SvcCost"}            ,{"Series"                ,"numSeries"}        }),

// retrieve relevant columns WikiCars02 = Table.SelectColumns(WikiCars01,       {             "Car"            ,"NLA"            ,"PR"            ,"Class"            ,"Type"            ,"numSeries"            ,"Cost"            ,"SvcTime"            ,"SvcCost"        }),

// change column types to text WikiCars03 = Table.TransformColumnTypes(WikiCars02,       {             {"Car"            ,type text}            ,{"NLA"            ,type text}            ,{"PR"             ,type text}            ,{"Class"          ,type text}            ,{"Type"           ,type text}            ,{"numSeries"      ,type text}            ,{"Cost"           ,type text}            ,{"SvcTime"        ,type text}            ,{"SvcCost"        ,type text}        }),

// replace ? and shifted-space #A0 and CR/LF with space // NOTE: not replacing CR/LF in Type field as it's checked for in the next step // NOTE: not replacing ? or - in TopSpeed, Acceleration, Braking and Grip fields as it will cause problems with next steps WikiCars09 = Table.ReplaceValue(       Table.ReplaceValue( Table.ReplaceValue(       Table.ReplaceValue( WikiCars03 , "?",        "",  Replacer.ReplaceText, {"NLA", "PR", "numSeries", "Cost", "SvcTime", "SvcCost"})       , "-",         "",  Replacer.ReplaceText, {"NLA", "PR", "numSeries", "Cost", "SvcTime", "SvcCost"}) ,"#(00A0)",   " ", Replacer.ReplaceText, {"Car", "PR", "Class", "numSeries", "Cost", "SvcTime", "SvcCost"} )        ,"#(cr)#(lf)", " ", Replacer.ReplaceText, {"Car", "PR", "Class", "numSeries", "Cost", "SvcTime", "SvcCost"} ) ,

// split Type field to get Type and additional info (e.g. RHD) WikiCars11 = Table.SplitColumn(       Table.ReplaceValue( WikiCars09 ,"#(cr)#(lf)", " ", Replacer.ReplaceText, {"Type"} )       ,"Type", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Type", "TypeInfo"}) ,

// split Cost to get Gold and R$ costs WikiCars19 = Table.SplitColumn(WikiCars11, "Cost",Splitter.SplitTextByDelimiter("R$", QuoteStyle.Csv), {"Gold", "Cash"} ),

// replace values WikiCars91 = Table.ReplaceValue(       Table.ReplaceValue( Table.ReplaceValue(       Table.ReplaceValue( WikiCars19 ,"!", "Y",  Replacer.ReplaceValue, {"NLA"} )        ,"",   "0",  Replacer.ReplaceValue, {"PR", "numSeries", "Gold", "Cash", "SvcCost"} ) ,"-", "0",  Replacer.ReplaceValue, {"PR", "numSeries", "Gold", "Cash", "SvcCost"} )        ,null, "0",  Replacer.ReplaceValue, {"PR", "numSeries", "Gold", "Cash", "SvcCost"} ) ,

// set numeric fields WikiCars92 = Table.TransformColumnTypes(WikiCars91,       {             {"PR"            ,type number}            ,{"numSeries"     ,type number}            ,{"Cash"          ,type number}            ,{"Gold"          ,type number}            ,{"SvcCost"       ,type number}         }),

// remove rows with errors and sort the table WikiCars93 = Table.RemoveRowsWithErrors(WikiCars92),

// add CarID to each car WikiCars95 = Table.AddIndexColumn(WikiCars93, "CarID", 1, 1),

// add generated Wiki Link WikiCars96 = Table.AddColumn(WikiCars95, "WikiLink", each fWikiLink([Car]) ),

// reorder the columns AND sort the table by Car WikiCars99 = Table.Sort(       Table.ReorderColumns( WikiCars96 ,{"Car", "CarID", "NLA", "PR", "Class", "Type", "numSeries", "Cash", "Gold", "SvcTime", "SvcCost", "WikiLink"} )       ,"Car") in WikiCars99 //==== in funcCarsFromWiki //