是否在Power Query中拆分列而不转换为文本？ [英] Split Column Without Converting to Text in Power Query?

问题描述

Initial table

是否可以将单元格拆分为多行，而无需将数据类型转换为文本？使用Power Query功能区中的"将列拆分为行"功能会将整个列转换为文本，并且我会丢失所需的数据类型值。我已尝试将列转换回类型"any"，但每个单元格仍为文本。

After splitting

推荐答案

尽管您在查询中显示了不同的类型，但在加载查询时，它将全部加载为文本(Excel和Power BI)。

表的格式看起来像是取消透视的强类型列。如果您将数据透视回具有单一类型的适当列，这将变得很简单。

在下面的查询中，前三个步骤将您的示例表返回到适当的列中，每列只有一种类型。

然后，我只需按分隔符拆分成行，然后设置类型。

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjKuMa4xNFDSUfLJLC5RitWJVjI2AfL8SnMNwTywHJBnBOYl5ZSmAvnO+Tn5RVBpfWN9Q0tLC6CoS2JJqlJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Value = _t, Column = _t]),
    #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[Column]), "Column", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Pivoted Column",{{"Num1", Int64.Type}, {"Num2", type number}, {"Date", type date}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"List", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "List"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"List", Int64.Type}})
in
    #"Changed Type1"

如果您需要按列拆分数据，则如下所示：

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjKuMa4xNFDSUfLJLC5RitWJVjI2AfL8SnMNwTywHJBnBOYl5ZSmAvnO+Tn5RVBpfWN9Q0tLC6CoS2JJqlJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Value = _t, Column = _t]),
    #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[Column]), "Column", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Pivoted Column",{{"Num1", Int64.Type}, {"Num2", type number}, {"Date", type date}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "List", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"List.1", "List.2", "List.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"List.1", Int64.Type}, {"List.2", Int64.Type}, {"List.3", Int64.Type}})
in
    #"Changed Type1"

这里的挑战是您必须选择拆分的最大列数。如果已知很简单，否则您可能需要计算列中"；|"；字符的最大数量。

这篇关于是否在Power Query中拆分列而不转换为文本？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！