Conditional Expression to Sum True Values Across Many Columns

[raywoc]

Greetings Hive Mind - I have a large data set with around 200 columns, many of which have pairs of groupings.  In PowerQuery, I am trying to have  a conditional function, where if true, will sum only those values from the true columns.

Above is a sample.  I essentially want the measure to pick up and sum the "Reported" value of each "Reported" column, where the condition is met that Reported > 0 and Validated = 0. 

So in the example above, the New Measure would be 200, summed from the Reported 1 and Reported 2 columns for Audit 1 and Report 2 and Reported 3 for Audit 2.

Thanks in advanced for all the help this forum provides! 

[wdx223_Daniel]

=Table.AddColumn(PreviousStepName,"Custom",each List.Sum(List.Zip(List.Select(List.Split(List.Skip(Record.ToList(_)),2),each _{1}=0)){0}))

[samdthompson]

@wdx223_Daniel , how do you find the performance impact of using List.Zip across 200 odd columns? I ve never used it for more than 20 odd but I found the impact pretty sizable so have shied away from it. That may have been my data of course. 

[wdx223_Daniel]

just provide a solution for the samle data, the efficiency is not always in my concern.

[samdthompson · Accepted solution]

Hello, try this. (obvioulsy the source etc might need to change but it just makes the data tall rather than wide, adds some logic and away you sgo with a simple SUM once you get to the DAX side)

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Audit"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", Int64.Type}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Attribute.1]), "Attribute.1", "Value"),
#"Added Custom" = Table.AddColumn(#"Pivoted Column", "ValueToSum", each if [Reported]>0 and [Validated]=0 then [Reported] else 0)
in
#"Added Custom"

cheers