在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_功能:
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] |
在ID#97的情况下,Vehicle2CapaScore和Vechile3CapaScore分别是2和10,而不是2两者的得分都为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的车辆。然后我将添加列并编写一个简单的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.
如果[Min Value] = 0,则为0,否则,[Sum Value]
这篇关于在Power BI查询(M)中添加特殊情况的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!