Forum Discussion
Counting Occurances Across Multiple Columns With If Statements
Hi Anonymous ,
I created a sample pbix file(see the attachment), please check if that is what you want.
1. Handle it in Power Query Editor first
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0ABFAbALhxOpEKzkBmQYoGCTqDGQZQTFC1AXIMoVqN4WLusJNRjIjFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Final = _t, FC1 = _t, FC2 = _t, FC3 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Final", Int64.Type}, {"FC1", Int64.Type}, {"FC2", Int64.Type}, {"FC3", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [FC1]=[Final] then Text.From( [FC1])&","&"," else if [FC2]=[Final] then Text.From( [FC1])&","&Text.From( [FC2])&"," else Text.From( [FC1])&","&Text.From( [FC2])&","&Text.From( [FC3])), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Final", "FC1", "FC2", "FC3"}), #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Custom", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"FC1", "FC2", "FC3"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"FC1", Int64.Type}, {"FC2", Int64.Type}, {"FC3", Int64.Type}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Name"}, "Attribute", "FC Mode") in #"Unpivoted Columns"2. Create a matrix visual
Best Regards
Hi Anonymous ,
Could you please provide some raw data and explain more details on how to get the count of [FC Mode]? I'm not clear that why the count of FC Mode 2 and 5 is 1?
Best Regards
Hi Anonymous,
The FC modes are the numbers that appear in the FC1, FC2, and/or FC3 columns. Starting at FC1, the counting stops if the FC mode value of the current row matches the value in the Final column of the same row.
For the two examples you circled:
- FC mode 2 appears once in row C. Since FC1= Final, we ignore FC2 and FC3 in row C.
- In row D, there are 1 occurance of 4 and 1 occurance of 5. Since FC2 = Final, we stop at FC2 and don't count FC3
I hope this makes sense. The data I provided is from the actual data but only includes a few entries.
Thanks!
Hi Anonymous ,
I created a sample pbix file(see the attachment), please check if that is what you want.
1. Handle it in Power Query Editor first
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0ABFAbALhxOpEKzkBmQYoGCTqDGQZQTFC1AXIMoVqN4WLusJNRjIjFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Final = _t, FC1 = _t, FC2 = _t, FC3 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Final", Int64.Type}, {"FC1", Int64.Type}, {"FC2", Int64.Type}, {"FC3", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [FC1]=[Final] then Text.From( [FC1])&","&"," else if [FC2]=[Final] then Text.From( [FC1])&","&Text.From( [FC2])&"," else Text.From( [FC1])&","&Text.From( [FC2])&","&Text.From( [FC3])), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Final", "FC1", "FC2", "FC3"}), #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Custom", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"FC1", "FC2", "FC3"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"FC1", Int64.Type}, {"FC2", Int64.Type}, {"FC3", Int64.Type}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Name"}, "Attribute", "FC Mode") in #"Unpivoted Columns"2. Create a matrix visual
Best Regards