时间戳

首页 > 解决方案 > 需要 Excel 帮助：具有多个表格的文本文件导入

excel - 需要 Excel 帮助：具有多个表格的文本文件导入

问题描述

我无法从网络上提取气象站数据。我正在访问一个文本文件，该文件将每个站点的所有数据都放在一个文件中。当我使用电源查询时，我能够提取所有数据，但它们都在一个工作表中。理想情况下，我可以添加一个列出站点名称的列，这样我就可以使用 V/Hlookup 函数，或者我可以重新格式化表格，以便每个站点的所有数据都在一行上。任何建议表示赞赏。 源文件的屏幕截图 操作后可能的 Excel 格式

这是源文件的链接：http ://wfas.net/nfdr/output/ndfd_predserv_fcst.txt

标签： excelpowerquery

解决方案

我建议将每个站点的名称和元数据（存储在第一行）提取到一个表中，并将天气数据提取到一个单独的表中，并将它们与Station ID列链接。如果元数据除了站名之外对您没有用处，则可以轻松地调整以下示例。

将文本文件导入 Power Query 编辑器后，首先需要拆分列。在以下示例中，这是通过使用位置来完成的，除了LOESS HILLS TNC BROK站名称比所有其他名称长一个字符导致元数据与列不对齐之外，它工作正常。我把解决这个问题的问题留给你。

以下示例是对从 2021 年 9 月 18 日星期六访问的链接源导入的数据样本执行的，其中仅包含前 3 个气象站。示例包含在下面的 M 代码中，前 20 行如下所示：

样本原始数据


这是用于准备数据的 M 代码，然后将其分成一张用于站元数据的表和一张用于天气数据的表。关键部分是为车站创建一个 ID 列。在名为 的步骤#"Integer-Divided Column"中，11*13对应于 11（测量天数）乘以 13（变量：站名、Fcst Dy、Max RH (%)等）：

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("1Zdfb9MwFMW/ilUJqUy02E6aOI9d29GKdZ3SSX2o9oBgCARMEwwJvj3pn3Nc+bZxRuBhedh+clwf+9q5x3e97pxdl7P5RE0Xi7fqalWqzZPrRJvqf+L6TmulevmgbzdgtTW6MHlFm/ed21frzsX7H49q/FsdPrp4bTJADnCAYg9WAwzAAhJAChgAtiNvtefvfqlyqrovXh6q94piB0brHRTZ/lWRAhKAFq/QmeNI2Kl/vlc3d98eVPfiQJ99coycOUAOSAUMQmiiXrP2BOrOYmQTwiALWzjnqHoV+bq1Y8kOceY0HCTy4mRLTH21fPigul/uH0/sO7QQS4XAV0ciBGgqTDmqfj5TRx72sViywYgW4bWZABwA/iqmPilHdeqGI2OBFtGgRA3E1GfHxH0frMZHlXvBibGlEJ1j6kb3Pn1XH3/efT0893LtBgGX2+3BhBBXPybv1bHdRwDnz89QQAP1I/L+1PGMSdBxiKn7P2fz2Xiyml2pm+HlpXpTDpfLKoSVPVRyqekn6cYwnOub5PkZhvcJQh6CNxWmtn9kGBkNAyMP8GV4n4AWc/eg6fGNGQYkUgyYYskpXnE+hBQfc0vDcNByUPctUKeF5GkILQ2D9kAPyERLjXO0NQwMRM+2dA7xKrEhtDQM7nuSCGAGwXzkNFoahk+RTIgwbe8chDyElobBzZVWJH3ib+yq3jAQeb9kCdI5ml4QI4Yhg/kkeIJhjMrhuVqUo+mwHO9rDGOKrWEkeT/bVhiu6OvnXGE0AVYYruk9N2YYvL/LCsMJ4K2/adKMGAaTgi90qI4z6jM1r+SN1SOGQfdlVNmiQ/DTaPrxRAyDhUXWAOgu/FVbw6BPiCuez6fMIEUILQ3DFw02BK/OFojapteUiGHI8DKqLD4wwSNJvG2FIT2J1R6z+ekq5P9VGP4kpGEL+7Q1DHGQ/ObKAymgVv32Dw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Split Column by Positions" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByPositions({0, 19, 26, 34, 45, 55, 65, 74, 83, 92, 101, 110}), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7", "Column1.8", "Column1.9", "Column1.10", "Column1.11", "Column1.12"}),
    #"Removed Blank Rows" = Table.SelectRows(#"Split Column by Positions", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Trimmed Text" = Table.TransformColumns(#"Removed Blank Rows",{{"Column1.1", Text.Trim, type text}, {"Column1.2", Text.Trim, type text}, {"Column1.3", Text.Trim, type text}, {"Column1.4", Text.Trim, type text}, {"Column1.5", Text.Trim, type text}, {"Column1.6", Text.Trim, type text}, {"Column1.7", Text.Trim, type text}, {"Column1.8", Text.Trim, type text}, {"Column1.9", Text.Trim, type text}, {"Column1.10", Text.Trim, type text}, {"Column1.11", Text.Trim, type text}, {"Column1.12", Text.Trim, type text}}),
    #"Unpivoted All Columns Except First One" = Table.UnpivotOtherColumns(#"Trimmed Text", {"Column1.1"}, "Attribute", "Value"),
    #"Added Index to Create Station ID" = Table.AddIndexColumn(#"Unpivoted All Columns Except First One", "Station ID", 0, 1, Int64.Type),
    #"Integer-Divided Column" = Table.TransformColumns(#"Added Index to Create Station ID", {{"Station ID", each Number.IntegerDivide(_, 11*13), Int64.Type}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Integer-Divided Column",{"Station ID", "Column1.1", "Attribute", "Value"})
in
    #"Reordered Columns"

这是前 20 行的结果：

数据准备


现在，您可以创建对此查询的引用（名为DataPrep）并使用该函数删除替代以创建仅包含电台元数据的表。可以在旋转表格之前过滤掉带有空白单元格的行。在以下代码中，132对应于 11（测量天数）乘以 12（变量：Fcst Dy、Max RH (%)等）：

let
    Source = DataPrep,
    #"Removed Alternate Rows" = Table.AlternateRows(Source,11,132,11),
    #"Filtered Rows" = Table.SelectRows(#"Removed Alternate Rows", each ([Value] <> "")),
    #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Attribute]), "Attribute", "Value")
in
    #"Pivoted Column"

这是前 4 列的结果，这些列仍然需要适当地重命名：

站元数据


包含天气数据的表以类似的方式创建，除了这次Column1.1列是旋转的列，并​​且-99在旋转表后过滤掉输入的缺失值以删除关联的日期：

let
    Source = DataPrep,
    #"Removed Alternate Rows" = Table.AlternateRows(Source,0,11,132),
    #"Pivoted Column" = Table.Pivot(#"Removed Alternate Rows", List.Distinct(#"Removed Alternate Rows"[Column1.1]), "Column1.1", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Pivoted Column", each ([#"Max RH (%)"] <> "-99")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Attribute"})
in
    #"Removed Columns"

这是前 20 行和前 4 列的结果：

天气数据


数据现在已准备好进行进一步处理或分析。可以将表加载到数据模型以使用 Power Pivot 创建数据透视表，从而在您共享的屏幕截图中获得结果。或者，您可以编辑上面的查询以获取气象数据表中的气象站名称而不是气象站 ID，然后仅从该表创建数据透视表，而不需要 Power Pivot。

推荐阅读