Here's a couple of power queries which will combine all four text sources, and filter out irrelevant values. It returns around 1.3 million rows - so I'd suggest loading to Power Pivot data model, rather than materialising the data on worksheet(s).
fnGetTxtData:
(SourceURL) =>
let
Source = Table.FromColumns({Lines.FromBinary(Web.Contents(SourceURL),null, null, 20127)}),
#"Removed Top Rows" = Table.Skip(Source,152),
#"Replaced Value" = Table.ReplaceValue(#"Removed Top Rows","# ","",Replacer.ReplaceText,{"Column1"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value", "Column1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv)),
#"Promoted Headers" = Table.PromoteHeaders(#"Split Column by Delimiter", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"site_code", type text}, {"year", Int64.Type}, {"month", Int64.Type}, {"day", Int64.Type}, {"hour", Int64.Type}, {"minute", Int64.Type}, {"second", Int64.Type}, {"value", type number}, {"value_unc", type number}, {"nvalue", Int64.Type}, {"latitude", type number}, {"longitude", type number}, {"altitude", type number}, {"elevation", type number}, {"intake_height", type number}, {"instrument", type text}, {"qcflag", type text}})
in
#"Changed Type"
CombinedData:
let
Source = Table.FromList({
"ftp://aftp.cmdl.noaa.gov/data/trace_gases/co2/in-situ/surface/brw/co2_brw_surface-insitu_1_ccgg_HourlyData.txt",
"ftp://aftp.cmdl.noaa.gov/data/trace_gases/co2/in-situ/surface/mlo/co2_mlo_surface-insitu_1_ccgg_HourlyData.txt",
"ftp://aftp.cmdl.noaa.gov/data/trace_gases/co2/in-situ/surface/smo/co2_smo_surface-insitu_1_ccgg_HourlyData.txt",
"ftp://aftp.cmdl.noaa.gov/data/trace_gases/co2/in-situ/surface/spo/co2_spo_surface-insitu_1_ccgg_HourlyData.txt"
},null,{"SourceURL"}),
#"Invoked Custom Function" = Table.AddColumn(Source, "fnGetTxtData", each fnGetTxtData([SourceURL])),
#"Combined Data" = Table.Combine(#"Invoked Custom Function"[fnGetTxtData]),
#"Filtered Rows" = Table.SelectRows(#"Combined Data", each ([value] <> -999.99))
in
#"Filtered Rows"
Attached file has query as connection only, to keep file size small.
Bookmarks