Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
raywoc
New Member

Conditional Expression to Sum True Values Across Many Columns

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.

 

sample table.png

 

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! 

1 ACCEPTED SOLUTION
samdthompson
Memorable Member
Memorable Member

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

// if this is a solution please mark as such. Kudos always appreciated.

View solution in original post

4 REPLIES 4
wdx223_Daniel
Super User
Super User

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

@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. 

 

// if this is a solution please mark as such. Kudos always appreciated.

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

samdthompson
Memorable Member
Memorable Member

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

// if this is a solution please mark as such. Kudos always appreciated.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors