#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Call", "Output", " It Was Here", "Input","Location", "Service Area", "Latitude", "Longitude", "Maidenhead", "S", "ERP", "HASL", "T/O", "Sp", "Tone", "Wavelength", "Technology", "Group"})
let
URL = "http://www.wia.org.au/members/repeaters/data/",
WebSource = Table.FromColumns({Lines.FromBinary(Web.Contents(URL))}),
FileSource = Text.Combine({URL,Text.BeforeDelimiter(Text.AfterDelimiter(Table.SelectRows(WebSource, each Text.Contains([Column1], ".csv")){0}[Column1],"href="""),".csv"),".csv"}),
Source = Csv.Document(Web.Contents(FileSource),[Delimiter=",", Columns=15, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Replaced nulls" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,Table.ColumnNames(Source)),
#"Replaced hyphens" = Table.ReplaceValue(#"Replaced nulls","-",null,Replacer.ReplaceValue,Table.ColumnNames(Source)),
#"Promoted Headers" = Table.PromoteHeaders(#"Replaced hyphens", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Output", type number}, {"Input", type number}, {"Latitude", type number}, {"Longitude", type number}, {"ERP", type number}, {"HASL", type number}, {"T/O", type number}, {"Tone", type number}}),
#"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type", List.Transform(Table.ColumnNames(#"Changed Type"), each {_, null})),
#"Added Maidenhead" = Table.AddColumn(#"Replaced Errors", "Maidenhead", each Maidenhead([Latitude], [Longitude]), type text),
#"Added Criteria" = Table.AddColumn(#"Added Maidenhead", "Criteria", each Text.BetweenDelimiters([Notes], "{{", "}}"), type text),
#"Replaced Null" = Table.ReplaceValue(#"Added Criteria","",null,Replacer.ReplaceValue,{"Criteria"}),
#"Filled Down" = Table.FillDown(#"Replaced Null",{"Criteria"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Filled Down", "Criteria", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Wavelength", "Technology"}),
#"Filtered Rows" = Table.SelectRows(#"Split Column by Delimiter", each [Call] <> null and [Call] <> ""),
#"Added Group" = Table.AddColumn(#"Filtered Rows", "Group", each if Text.Contains(Text.Upper([Service Area]), "WICEN") then "Wicen" else if [Wavelength] = "23CM" then "VK23cm" else if [Technology] = "FM" then Text.Start([Call],3) else "VK" & [Technology], type text),
#"Removed Columns" = Table.RemoveColumns(#"Added Group",{"Notes"}),
#"Removed Columns" = Table.RemoveColumns(#"Added ",{"nMemonic"}), <<<<<<<<<<<<<<<<<<<<<< Added Instruction for removal
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Call", "Output", "Input","Location", "Service Area", "Latitude", "Longitude", "Maidenhead", "S", "ERP", "HASL", "T/O", "Sp", "Tone", "Wavelength", "Technology", "Group"})
in
#"Reordered Columns"
Peter
Bookmarks