Count Multiple Columns

Hi vincentakatoh 

You should unpivot the partNumber columns in the query editor 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0MDAwNDIGsvJKc3KwU7E60UpGcLUmKLogLFM4ywzOMgfrQ6ixIGQbSLkJFmtMcStHtVcBBcfGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Case_Number = _t, #"PartNumber_Part replace_1st" = _t, #"PartNumber_Part replace_2nd" = _t, #"PartNumber_Part replace_3rd" = _t, #"PartNumber_Part replace_4th" = _t, #"PartNumber_Part replace_5th" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Case_Number", Int64.Type}, {"PartNumber_Part replace_1st", Int64.Type}, {"PartNumber_Part replace_2nd", Int64.Type}, {"PartNumber_Part replace_3rd", Int64.Type}, {"PartNumber_Part replace_4th", Int64.Type}, {"PartNumber_Part replace_5th", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Case_Number"}, "Attribute", "Value")
in
    #"Unpivoted Columns"

to get something like this. Fromt that you ance get what you want easily:

Case_NumberAttributeValue

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs

Cheers