Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Counting Occurances Across Multiple Columns With If Statements

Hello,   I was wondering if I could get some helps with a problem. I have a table labeled Original below. I would like to find the occurances of the FC modes by first find if the values of the Fin...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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