Power Query: Adding a Special Case

0 votes

Power BI's query editor is my choice of editor to unpivot, transform, join, re-pivot and re-join the data that I have. 

REF_Capability:

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

Table2:

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     |

By combining functions like unpivoting, filtering, merging queries, expanding columns and re-pivoting and re-merging This is the OutputTable I end up with:

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]     |

In the case of ID#97 under Vehicle2CapaScore and Vechile3CapaScore instead of scores of 2 and 10 respectively I would like a score of 0 for both. 

So, basically what I mean is that a Vehicle having an outright score of 0 for any particular capability shall score a 0 for all capabilities. Simply put, it doesn't matter how well you run if you can't crawl.

Some help with working that logic into my query would be really nice.

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"

Dec 27, 2018 in Power BI by Shubham
• 13,350 points
52 views

1 answer to this question.

0 votes

Try adding a Group By with Minimum calculation to find Vehicles with a 0. Then you can write a simple if statement for a new column e.g.

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

answered Dec 27, 2018 by Upasana
• 8,550 points

Related Questions In Power BI

0 votes
1 answer

How can I get a column value from previous row in Power Query?

Hi Sindhu, add this line as your ...READ MORE

answered Mar 18 in Power BI by Cherukuri
• 32,260 points
1,324 views
+1 vote
1 answer

Is there a way to convert JSON data to readable table in power bi

Follow these steps step 1 - Click on ...READ MORE

answered Sep 24, 2018 in Power BI by Hannah
• 17,060 points
3,102 views
0 votes
1 answer

How to assign Power BI license to a new user

The assign license method of MS Graph API might ...READ MORE

answered Sep 25, 2018 in Power BI by Kalgi
• 45,780 points
76 views
0 votes
1 answer

Can I create a Power BI report in Office 365 without my locally installed Office Excel?

Yess!! You can achieve this by using ...READ MORE

answered Sep 26, 2018 in Power BI by Kalgi
• 45,780 points
98 views
0 votes
1 answer

Replace “yes” found in one list with the actual string from another list

Objective : The idea is to use List.Transform twice, ...READ MORE

answered Feb 19 in Power BI by Upasana
• 8,550 points
50 views
0 votes
1 answer

Cumulative row data over last 12 months in Power Query

Basically, what you do is,  Add an Index,  Group ...READ MORE

answered Apr 1 in Power BI by Upasana
• 8,550 points
399 views
0 votes
1 answer

How to export Power Queries from One Workbook to Another with VBA?

Try solving it using the Workbook. Query ...READ MORE

answered Oct 22, 2018 in Power BI by Annie97
• 2,190 points
529 views
0 votes
1 answer

Excel Power Query: Using List.MatchAny on a column value

try this. let TableA = ...READ MORE

answered Oct 22, 2018 in Power BI by Annie97
• 2,190 points
356 views
0 votes
1 answer

Power Query : Adding Columns and Multiple files

The following call, FirstRowAsHeader = Table.PromoteHeaders(TableWithoutHeader) shall replace the ...READ MORE

answered Feb 14 in Power BI by Upasana
• 8,550 points
38 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,550 points
349 views