向 Power BI 查询添加特殊情况 (M) [英] Add Special Case to Power BI Query (M)

问题描述

我目前正在使用 Power BI 的查询编辑器来取消透视、转换、连接、重新透视和重新连接一些数据.说明:

I'm currently using Power BI's query editor to unpivot, transform, join, re-pivot and re-join some data. Illustrating:

REF_Capability:

REF_Capability:

ID#| Capability.1 | Capability.2 | Capability.3| 
97 | Crawl        | Walk         | Run         |
98 | Crawl        | null         | null        |
99 | Crawl        | Walk         | null        |

表2:

Capability | Attribute| Value | 
Crawl      | Vehicle1 | 4     |
Walk       | Vehicle1 | 3     |
Run        | Vehicle1 | 2     |
Crawl      | Vehicle2 | 0     |
Walk       | Vehicle2 | 1     |
Run        | Vehilce2 | 1     |
Crawl      | Vehicle3 | 0     |
Walk       | Vehicle3 | 5     |
Run        | Vehicle3 | 5     |

结合使用反透视、过滤、合并查询、扩展列以及重新透视和重新合并，我得到了这个 OutputTable:

Using a combination of unpivoting, filtering, merging queries, expanding columns and re-pivoting and re-merging I get this OutputTable:

ID#| Capability.1 | Capability.2 | Capability.3| Score.Vehicle1    | Score.Vehicle2    | Score.Vehicle3   | 
97 | Crawl        | Walk         | Run         | 9 [4+3+2]         | 2 [0+1+1]         | 10 [0+5+5]       |
98 | Crawl        | null         | null        | 4 [4+null+null]   | 0 [0+null+null]   | 0 [0+null+null]  |
99 | Crawl        | Walk         | null        | 7 [4+3+null]      | 1 [0+1+null]      | 5 [0+5+null]     |

对于 Vehicle2CapaScore 和 Vechile3CapaScore 下的 ID#97 而不是分别为 2 和 10 的分数，我希望两者的分数均为 0.解释...如果车辆在任何特定能力上的得分为 0，则它在所有能力上得分为 0.或者更简单地说，如果你不会爬行，那么你在步行或跑步方面的表现并不重要.

In the case of ID#97 under Vehicle2CapaScore and Vechile3CapaScore instead of scores of 2 and 10 respectively I would like score of 0 for both. Explaining... if a Vehicle has an outright score of 0 for ANY particular capability it scores a 0 for all capabilities. Or more simply, it doesn't matter how good you are at walking or running if you can't crawl.

我需要一些帮助，将这个逻辑应用到我的查询中:

I would like some help working that logic into my query:

let
    Source = OData.Feed("SOURCE_vti_bin/listdata.svc"),
    REF_Capability = Source{[ID#="REF_Capability",Signature="table"]}[Data],
    #"Split Column by Delimiter" = Table.SplitColumn(Table1,"Capability",Splitter.SplitTextByDelimiter("; ", QuoteStyle.Csv),{"Capability.1", "Capability.2", "Capability.3"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Capability.1", type text}, {"Capability.2", type text}, {"Capability.3", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Change Type", {"ID#"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Attribute] = "Capability.1" or [Attribute] = "Capability.2" or [Attribute] = "Capability.3") and ([Value] <> "")),
    #"Merged Queries" = Table.NestedJoin(#"Filtered Rows",{"Value"},Table2,{"Capability"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Attribute", "Value"}, {"VehicleScore.Attribute", "VehicleScore.Value"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded NewColumn",{"Attribute", "Value"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[VehicleScore.Attribute]), "VehicleScore.Attribute", "VehicleScore.Value", List.Sum)
in
    #"Pivoted Column"

let
    Source = OData.Feed("SOURCE/_vti_bin/ListData.svc"),
    OutputTable = Source{[ID#="OutputTable",Signature="table"]}[Data],
    #"Merged Queries" = Table.NestedJoin(#OutputTable,{"ID#"},REF_Capability,{"ID#"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Vehicle1", "Vehicle2", "Vehicle3"}, {"Score.Vehicle1","Score.Vehicle2", "Score.Vehicle3"})
in
    #"Expanded NewColumn"

推荐答案

我会添加一个带有 Min calc 的 Group By 来找到带有 0 的 Vehicles.然后我会添加一个 Column 并编写一个简单的 if 语句，例如

I would add a Group By with Min calc to the to find Vehicles with a 0. Then I'd Add a Column and write a simple if statement e.g.

if [Min Value] = 0 then 0 else [Sum Value]

这篇关于向 Power BI 查询添加特殊情况 (M)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！