Forum Discussion

prashantg364's avatar
prashantg364
Icon for Helper II rankHelper II
3 years ago

cutom column

I want to add a custom column in the power query editor to get output for the count of rows of the "Text" column.

Example as shown below... 

 

TextCustom column
AAACCC3
AAACCC3
AAACCC3
AAMTHN2
AAMTHN2
AATSSH1
AATSYC1
AAYSDN2
AAYSDN2
Need Help

3 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Icon for Most Valuable Professional rankMost Valuable Professional
    3 years ago

    You can use this

    = List.Count(List.Select(List.Buffer(Source[Text]), (x)=>x=[Text]))
    • AlienSx's avatar
      AlienSx
      Icon for Super User rankSuper User
      3 years ago

      Hi, Vijay_A_Verma x =[Text] is not going to work. Imho better idea is to add one extra step to buffer Source[Text] first and then (in the scope of Table.AddColumn function) this should look like this

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnR0dHZ2VorVwc30DfHww2SGBAd7wJmRMLWRwS5+KMxYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Text = _t]),
    text_column = List.Buffer(Source[Text]),
    f = 
        Table.AddColumn(
            Source, 
            "Custom Column",
            each List.Count(List.Select(text_column, (x)=>x=[Text]))
        )
in
    f