Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to 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
=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.
just provide a solution for the samle data, the efficiency is not always in my concern.
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