excel 基于列的值将表拆分为多个较小的表-Power Query

uhry853o 于 2022-12-30 发布在其他

关注(0)|答案(2)|浏览(363)

我有一个这样的表，我得到了使用"合并和编辑"选项，在电源查询，从多个.xlsx文件的多个表的信息。表名永远不会改变，他们将保持不变，excel文件可以改变。

现在，我想用column1的值firstkey拆分许多表，这样，我可以得到多个表，

我一直在谷歌上寻找答案，仍然没有成功。有像this这样的线程，需要你复制原始表并过滤每个值。
然而，在我的例子中，如果我有新的.xlsx文件，我希望在某种程度上自动化，所以，如果我得到一个值Brooklyn Park而不是Bursville，它应该基于Column1的值进行过滤。
如何执行此超级查询？

- 编辑**

根据要求，一份文件的原始excel表，

M代码：

let
    Source = Excel_Export,
    #"Trimmed Text" = Table.TransformColumns(Source,{{"Column1", Text.Trim, type text}}),
    #"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"Column1", Text.Clean, type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Cleaned Text", each ([Source.Name] = "Burnsville.xlsx")),
    #"Transposed Table" = Table.Transpose(#"Filtered Rows"),
    #"Removed Top Rows" = Table.Skip(#"Transposed Table",1),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"Address", "Address Number"}, {"Column3", "StreetName"}, {"Column4", "City"}})
in
    #"Renamed Columns"

我使用这段代码创建了一个函数，以自动处理每个文件。

excel

来源：https://stackoverflow.com/questions/53454415/split-a-table-into-multiple-smaller-tables-based-on-a-columns-value-power-query

2条答案

按热度按时间

gudnpqoy1#

您发布的M代码表明至少有3列，但您的第一张图片只显示了两列。它似乎还引用了另一个查询（Excel_Export）。我希望它显示您是如何实现第一张图片中的表的，所以不太确定发生了什么。
关于空行的插入，您可以尝试以下功能。
代码：

fxInsertBlankRows = (tableToTransform as table) =>
    let
        blankRowToInsert = 
            let
                headers = Table.ColumnNames(tableToTransform),
                emptyTable = Table.FromColumns(List.Transform(headers, each {""}), headers),
                toListOfRecords = Table.ToRecords(emptyTable)
            in
                toListOfRecords,
        insertionIndexes =
            let
                isolateColumn = Table.SelectColumns(tableToTransform, {"Column1"}),
                indexes = Table.PositionOf(isolateColumn, [Column1="firstKey"], Occurrence.All)
            in
                indexes,
        insertBlankRows = List.Accumulate(insertionIndexes, tableToTransform, (tableState, currentIndex) =>
                Table.InsertRows(tableState, currentIndex, blankRowToInsert)
            ) 
    in
        insertBlankRows,

假设你想在你发布的M代码中的#"Renamed Columns"步骤中使用上面的函数（假设#"Renamed Columns"是一个表，我很确定它是一个表），你可以按如下所示修改代码的结束方式：

#"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"Address", "Address Number"}, {"Column3", "StreetName"}, {"Column4", "City"}})
fxInsertBlankRows = (tableToTransform as table) =>
        let
            blankRowToInsert = 
                let
                    headers = Table.ColumnNames(tableToTransform),
                    emptyTable = Table.FromColumns(List.Transform(headers, each {""}), headers),
                    toListOfRecords = Table.ToRecords(emptyTable)
                in
                    toListOfRecords,
            insertionIndexes =
                let
                    isolateColumn = Table.SelectColumns(tableToTransform, {"Column1"}),
                    indexes = Table.PositionOf(isolateColumn, [Column1="firstKey"], Occurrence.All)
                in
                    indexes,
            insertBlankRows = List.Accumulate(insertionIndexes, tableToTransform, (tableState, currentIndex) =>
                    Table.InsertRows(tableState, currentIndex, blankRowToInsert)
                ) 
        in
            insertBlankRows,
invokeFunction = fxInsertBlankRows(#"Renamed Columns")
in
    invokeFunction

赞(0）回复(0）举报 2022-12-30

wko9yo5t2#

看起来是个有趣的挑战。这里有一个独立的例子，我试着让它简洁一些：

let
    SourceTable = Table.FromRecords({
        [Cities = "City1", Info = "Info1"],[Cities = "City1", Info = "Info2"],
        [Cities = "City1", Info = "Info3"],[Cities = "City2", Info = "Info1"],
        [Cities = "City2", Info = "Info2"],[Cities = "City3", Info = "Info1"],
        [Cities = "City3", Info = "Info2"],[Cities = "City3", Info = "Info3"],
        [Cities = "City3", Info = "Info4"],[Cities = "City3", Info = "Info5"]
    }),
    SortedTable = Table.Sort(SourceTable,{{"Cities", Order.Ascending},{"Info", Order.Ascending}}),
    DistinctCities = List.Distinct(SortedTable[Cities]),
    DistinctCitiesAfterFirst = if List.Count(DistinctCities) > 1 then List.RemoveRange(DistinctCities,0) else {},
    CityOffsets = List.Transform(DistinctCitiesAfterFirst, each (List.PositionOf(SortedTable[Cities],_) + List.PositionOf(DistinctCitiesAfterFirst,_) - 1)),
    SortedTableWithBlankRows = List.Accumulate(
        CityOffsets,
        SortedTable,
        ((tableState, currentOffset) =>
            Table.InsertRows(
                tableState,
                currentOffset,
                {
                    Record.FromList(List.Repeat({""},Table.ColumnCount(SortedTable)),Table.ColumnNames(SortedTable))
                }
            )
        )
    )
in
    SortedTableWithBlankRows

赞(0）回复(0）举报 2022-12-30

我来回答

excel 基于列的值将表拆分为多个较小的表-Power Query

2条答案

相关问题

热门标签

最新问答