如何构建反向追溯矩阵

Question

我有一张包含需求和规范之间可追溯性矩阵的工作表，并且需要能够生成反向可追溯性，即列出每个规范的所有需求。例如：

每项要求的规格清单

我想制作类似的东西：每个规范的要求列表

知道如何做吗？

Answer 1

以下是使用 Power Query 执行此操作的一种方法。

使用 Power Query

• 在数据表中选择一些单元格
• Data => Get&Transform => from Table/Range
• 当 PQ 编辑器打开时：Home => Advanced Editor
• 记下表格姓名在第 2 行
• 将下面的 M 代码粘贴到您所看到的位置
• 将第 2 行的表名改回最初生成的表名。
• 阅读评论并探索Applied Steps以了解算法

M 代码

let

//Change table name in next line to the actual table name in your workbook
    Source = Excel.CurrentWorkbook(){[Name="Table34"]}[Content],

//set data types
    #"Changed Type" = Table.TransformColumnTypes(Source, List.Transform(Table.ColumnNames(Source), each {_, Text.Type})),

//Unpivot columns other than the 'REQ' column
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Req"}, "Attribute", "spec"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),

//Group by 'spec'
//  then generate a semicolon delimited list for `Req's`
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"spec"}, {
        {"Req", each Text.Combine([Req],";")}
        }),

//Generate listof column names for the Table.SplitColumn function
    maxCols =  List.Accumulate(#"Grouped Rows"[Req],
                    0, 
                    (state, current)=> 
                        let 
                            x =List.Count(Text.PositionOf(current,";",Occurrence.All)) + 1
                        in 
                            if x > state then x else state),
    colNamesList = List.Transform({1..maxCols}, each "Req " & Number.ToText(_)),

//sort by 'spec'
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"spec", Order.Ascending}}),

//Split by semicolon
    #"Split Column by Delimiter" = Table.SplitColumn(#"Sorted Rows", "Req", 
        Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv),colNamesList),

//set data types
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter", 
        List.Transform(Table.ColumnNames(#"Split Column by Delimiter"), each {_, Text.Type}))
in
    #"Changed Type1"

数据

结果