Forum Discussion

tgjones43's avatar
tgjones43
Icon for Helper IV rankHelper IV
7 years ago
Solved

Count text values across multiple columns for each row

Hello all, I have searched the forum for a solution to this but haven't managed to find what I need.   I have the following dataset and need to count the occurrences of each two letter code in colu...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    tgjones43 

     

    In that case

     

    =List.Count(
                 List.Select(
  Text.Split(
Text.Combine(
List.Transform(Record.ToList(_),each Text.From(_))
        ,",")
,
","
                ),each _ ="CL")
)
EugenioJunior's avatar
EugenioJunior
Frequent Visitor
7 years ago

Hi, tgjones43 

 

What if you slice when there are two records for the same column?

  • tgjones43's avatar
    tgjones43
    Icon for Helper IV rankHelper IV
    7 years ago

    Hi EugenioJunior, yes I could do that. So in the example, [Spot 10] is transformed into two columns; [Spot 10.1] and [Spot 10.2]

    • EugenioJunior's avatar
      EugenioJunior
      Frequent Visitor
      7 years ago

      So it worked?

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Icon for Community Champion rankCommunity Champion
        7 years ago

        tgjones43 EugenioJunior 

         

        Just another way could be yo use this Custom Column

         

        For example for CL

         

        =List.Count(
List.Select(
Text.Split(
Text.Combine(Record.ToList(_),","),
","
),each _ ="CL"))