如何解决在excel或Power中使用“分隔符”特性后列中不一致和无组织的数据的问题？

Question

!！这是我关于堆栈溢出的第一个问题，所以我提前为任何含糊的声明道歉!！

input data问题:由于中缺少信息，每个列中的数据不一致和无组织

我将使用以下术语：

• 输入数据:在Power BI
• 输出数据中应用“分隔定界符”功能之前列中的数据:在Power BI

中应用“分隔定界符”功能后列中的数据

问题

“我正在Power上工作，以正确地构造数据集”

Power 中数据集的输入数据

​

Power 中数据集的输出数据

​

“正如您在图片中看到的那样，我有一个列，其中的多个信息以上述格式(标题:info_title:info_title:info_~_

真实问题

“每个列现在都有应该属于另一列的值。这是因为输入数据的每个单元格中缺少信息(title:info)对。因此，多个单元格跳过一个列的下一个(title:info)对，导致一列中充满异构(title:info)对”

例如，：-

名为“ProductDetails.13”的

• 列现在有多对值，如"Qty Available:12“、"Qty Invoiced:2”、"Qty Invoiced:10“、"Qty Canceled:15”，而不是只有1组"Qty :0“

参考代码

M Language

    let
        #"Split Column by Delimiter" = Table.SplitColumn(#"Reordered Columns1", 
        "Product Details", Splitter.SplitTextByDelimiter("|",QuoteStyle.Csv),
        {"Product Details.1", "Product Details.2", "Product Details.3",
        "Product Details.4", "Product Details.5", "Product Details.6", 
        "Product Details.7", "Product Details.8", "Product Details.9", 
        "Product Details.10", "Product Details.11", "Product Details.12", 
        "Product Details.13", "Product Details.14", "Product Details.15", 
        "Product Details.16", "Product Details.17", "Product Details.18", 
        "Product Details.19", "Product Details.20"})
    in
        #"Split Column by Delimiter"

对解决方案的请求

请帮助我，建议一些方法来处理这样的异构数据，使其一致，并且Homogenous

• Help me将无效(标题:info)对移到它的右列，而不影响有效数据，留下一个空值或空白来代替无效的数据

。

预期结果

具有类似(标题:info)对consistently的同质数据的

• 列

备注：

incompetency

• I在
• 之前曾在

中遇到过类似的问题，我知道这个问题与MS excel或POWER BI

• 没有任何关系。

样本：

按照@

先生的要求，我无法以csv、xlsx、txt格式提供任何适当的示例文件，因为堆栈溢出不允许这样做，但我将尝试用一个简单的引用来解释我的问题

期望值:所有(title:info)数据都应该是同步的。

|    Header-1    |    Header-2    |    Header-3    |    Header-4    |    Header-5    |
|    Name:abc    |    SKU:1234    |    order:a1    |   invoice:1a   |   Shipment:0   |
|    Name:eef    |    SKU:5678    |    order:b2    |   invoice:2b   |   Shipment:1   |
|    Name:ghi    |    SKU:1256    |    order:c3    |   invoice:3c   |   Shipment:0   |
|    Name:jkl    |    SKU:3478    |    order:d4    |   invoice:4d   |   Shipment:1   |

现实:第3、第4和第5列的(标题:info)数据在整个列中不一致

|    Header-1    |    Header-2    |    Header-3    |    Header-4    |    Header-5    |
|    Name:abc    |    SKU:1234    |    order:a1    |   Shipment:0   |  available:N0  |
|    Name:eef    |    SKU:5678    |    order:b2    |   invoice:2b   |   Shipment:1   |
|    Name:ghi    |    SKU:1256    |  available:N0  |   price:2344   | Discount:0.02% |
|    Name:jkl    |    SKU:3478    |    order:d4    |   invoice:4d   |   Shipment:1   |

我希望现在一切都清楚了

Answer 1

不确定你到底想要什么结果，但是要从你发布的数据中创建，

• ，假定每一行代表特定的产品

，其中的标头是数据类型

• 的表。

如果这不是你想要的，请澄清。

您可以：

number

• unpivot

• 拆分数据管道上的

• 删除空列

• ，添加一列表示行

• ，另一列表示冒号

• 的值列，在值列上选择“不要聚合”

F 219

M码

阅读注释，以更好地理解算法。

更改第2行的表名()

let

//just preprocessing to get from what you posted
//  to pipe-separated table
    Source = Excel.CurrentWorkbook(){[Name="Table25"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"|    Header-1    |    Header-2    |    Header-3    |    Header-4    |    Header-5    |", type text}}),
    #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type1", "Column1", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", type text}, {"Column1.4", type text}, {"Column1.5", type text}, {"Column1.6", type text}, {"Column1.7", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"Column1.1", "Column1.7"}),
    #"Trimmed Text" = Table.TransformColumns(#"Removed Columns",{{"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}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Trimmed Text", [PromoteAllScalars=true]),
    #"Changed Type3" = Table.TransformColumnTypes(#"Promoted Headers",{{"Header-1", type text}, {"Header-2", type text}, {"Header-3", type text}, {"Header-4", type text}, {"Header-5", type text}}),

//Add index column to retain original row numbers
    rowNums = Table.AddIndexColumn(#"Changed Type3","Row Number",0,1,Int64.Type),

//Unpivot except for rowNum column
//remove Attribue column
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(rowNums, {"Row Number"}, "Attribute", "Value"),
    #"Removed Columns1" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),

//split by the colon delimiter
//set data types
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Removed Columns1", "Value", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Value.1", "Value.2"}),
    #"Changed Type4" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Value.1", type text}, {"Value.2", type text}}),

//Pivot on the Value.1 column
//Remove the row number column
    #"Pivoted Column" = Table.Pivot(#"Changed Type4", List.Distinct(#"Changed Type4"[Value.1]), "Value.1", "Value.2"),
    #"Removed Columns2" = Table.RemoveColumns(#"Pivoted Column",{"Row Number"})
in
    #"Removed Columns2"

​

​