Forum Discussion

vincentakatoh's avatar
vincentakatoh
Helper IV
6 years ago

Count Multiple Columns

Hi, Need help to count the part usage. Below is sample data,   Case_Number PartNumber_Part replace_1st PartNumber_Part replace_2nd PartNumber_Part replace_3rd PartNumber_Part replace_4th P...
Need Help
AlB's avatar
AlB
Community Champion
6 years ago

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

1 PartNumber_Part replace_1st 1000123
2 PartNumber_Part replace_1st 1000124
2 PartNumber_Part replace_2nd 1000123
2 PartNumber_Part replace_3rd 1000125
2 PartNumber_Part replace_4th 1000126
2 PartNumber_Part replace_5th 1000127
3 PartNumber_Part replace_1st 1000128
3 PartNumber_Part replace_2nd 1000123
4 PartNumber_Part replace_1st 1000124
4 PartNumber_Part replace_2nd 1000125
5 PartNumber_Part replace_1st 1000126

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 

 

 

  • vincentakatoh's avatar
    vincentakatoh
    Helper IV
    6 years ago

    AlB 

    Thanks for the response, the original data has several more columns. Unpivoting will mess up other calculations which I'm trying to avoid. Please advise if theres alternate method.

     

    Original data also has 100s of different part numbers.

     

    Thanks.

    • v-lid-msft's avatar
      v-lid-msft
      Community Support
      6 years ago

      Hi vincentakatoh ,

       

      We can use the following steps to meet your requirement:

       

      1. create a custom column in fact table:

       

      "," & Text.Combine(List.Transform(Record.ToList(Record.SelectFields(_,List.Select(Table.ColumnNames(NameOfYourLastStep), each Text.Contains(_, "PartNumber")))), Text.From), ",") & ","

       

       

      2. Create another query table:

       

      let
    Source = Table.SelectColumns(FactTable,List.Select(Table.ColumnNames(FactTable), each Text.Contains(_, "PartNumber"))),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
    #"Removed Other Columns" = Table.SelectColumns(#"Unpivoted Columns",{"Value"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns")
in
    #"Removed Duplicates"

       

       

      3. create a measure to calculate the result:

       

      Count = SUMX(DISTINCT('Query1'[Value]),var v=[Value] return SUMX('FactTable',round(DIVIDE(LEN([Custom])-LEN(SUBSTITUTE([Custom],","&v&",","")),LEN(","&v&","),0),0)))

       

       

      we use the Text.Contains(_, "PartNumber") to determine the partnumber column, if mean the condition of column name is it contain "Part Number", you can use Text.StartsWith(_, "PartNumber_Part replace_") , it will be more Strict.


      By the way, PBIX file as attached.


      Best regards,