Forum Discussion
Solved
How to count columns with particular values
Hi, I am trying to count difference coulms with 0 value in it. output column name is Count 0. Count 0 = COUNTIF([#"A - B"] = 0,[#"C - D"] = 0,[#"E - F"] = 0) It doen't work. Please suugest some ...
Hi jaydesai28,
You may try to achieve this requirement via Power Query.
let Source = Excel.Workbook(File.Contents("C:\Users\xxxx\Desktop\Sample Data.xlsx"), null, true), #"Test data_Sheet" = Source{[Item="Test data",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(#"Test data_Sheet", [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Id", Int64.Type}, {"A", Int64.Type}, {"B", Int64.Type}, {"A-B", Int64.Type}, {"C", Int64.Type}, {"D", Int64.Type}, {"C-D", Int64.Type}, {"E", Int64.Type}, {"F", Int64.Type}, {"E-F", Int64.Type}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Id", "A", "B", "C", "D", "E", "F"}, "Attribute", "Value"), #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Custom", each if [Value]=0 then 1 else 0), #"Added Custom2" = Table.AddColumn(#"Added Custom", "TotalCount", (This) => List.Sum(Table.SelectRows(#"Added Custom",each [Id] = This[Id])[Custom])), #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Custom"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute]), "Attribute", "Value", List.Sum) in #"Pivoted Column"I have uploaded the test .pbix file for your reference. Please check the "Applied Steps" in Query Editor mode.
Best regards,
Yuliana Gu
Hi jaydesai28,
You may try to achieve this requirement via Power Query.
let
Source = Excel.Workbook(File.Contents("C:\Users\xxxx\Desktop\Sample Data.xlsx"), null, true),
#"Test data_Sheet" = Source{[Item="Test data",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"Test data_Sheet", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Id", Int64.Type}, {"A", Int64.Type}, {"B", Int64.Type}, {"A-B", Int64.Type}, {"C", Int64.Type}, {"D", Int64.Type}, {"C-D", Int64.Type}, {"E", Int64.Type}, {"F", Int64.Type}, {"E-F", Int64.Type}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Id", "A", "B", "C", "D", "E", "F"}, "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Custom", each if [Value]=0 then 1 else 0),
#"Added Custom2" = Table.AddColumn(#"Added Custom", "TotalCount", (This) => List.Sum(Table.SelectRows(#"Added Custom",each [Id] = This[Id])[Custom])),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Custom"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute]), "Attribute", "Value", List.Sum)
in
#"Pivoted Column"
I have uploaded the test .pbix file for your reference. Please check the "Applied Steps" in Query Editor mode.
Best regards,
Yuliana Gu
Hi v-yulgu-msft
Thanks for contributing to this post. I tried this strategy for a similar data set and was successful with the counting procedure, but I am getting duplicates in my data set after the Pivot step. I believe the List.Distinct fuction is supposed to take care of that, so I must have something entered wrong. My code is below, do you have any insight into how I can fix this? I removed columns names from the first few steps for brevity.
let
Source = Excel.Workbook(Web.Contents(Path & File, [Timeout=#duration(0, 0, 2, 0)]), null, true),
Worksheet2 = Source{[Name="Worksheet1"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Worksheet2, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers"),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type"),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns"),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Name"}, "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "L1Yes", each if [Value] = "Yes" and Text.Contains([Attribute], "L1") then 1 else 0 ),
#"Added Custom2" = Table.AddColumn(#"Added Custom", "L1No", each if [Value] = "No" and Text.Contains([Attribute], "L1") then 1 else 0 ),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "L1NA", each if [Value] = "N/A" and Text.Contains([Attribute], "L1") then 1 else 0 ),
#"Added Custom4" = Table.AddColumn(#"Added Custom3", "CountL1Yes", (This) => List.Sum(Table.SelectRows(#"Added Custom3",each [Name] = This[Name])[L1Yes])),
#"Added Custom5" = Table.AddColumn(#"Added Custom4", "CountL1No", (This) => List.Sum(Table.SelectRows(#"Added Custom4",each [Name] = This[Name])[L1No])),
#"Added Custom6" = Table.AddColumn(#"Added Custom5", "CountL1NA", (This) => List.Sum(Table.SelectRows(#"Added Custom5",each [Name] = This[Name])[L1NA])),
#"Pivoted Column" = Table.Pivot(#Added Custom6", List.Distinct(#"Removed Columns2"[Attribute]), "Attribute", "Value", List.Sum),
#"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"L1.1", "L2.1", "L2.2", "L2.3", "L2.4", "L2.5", "L2.6", "L2.7", "L2.8", "L2.9", "L2.10", "L2.11", "L3.1", "L3.2", "L3.3", "L3.4", "L3.5", "L3.6", "L3.7", "L3.8", "L3.9", "L3.10", "L3.11", "L4.1"}),
#"Added Custom1" = Table.AddColumn(#"Removed Columns1", "L1PercentComplete", each [CountL1Yes]/([CountL1Yes]+[CountL1No])),
in
#"Added Custom1"