Forum Discussion

TonyHansson's avatar
TonyHansson
Frequent Visitor
2 years ago
Solved

Count calculated columns

I have table.   Origin2 D8000 D8000 D9000 D9000 D9000   I want add a column making a calculated count.   Origin2    Count D8000         1 D8000         2 D9000         1 D9000         ...
Need Help
  • TonyHansson's avatar
    TonyHansson
    2 years ago

    Hi

     

    Found this youtube:

     

    = Table.AddColumn(BufferedTable, "RunningCount", (OT) => Table.RowCount( Table.SelectRows(BufferedTable, (IT) => IT[Transaction No] <= OT[Transaction No] and IT[Origin2] = OT[Origin2])))

     

    Thank you for help

     

     

AlienSx's avatar
AlienSx
Super User
2 years ago
let
    Source = your_table,
    group = Table.Group(Source, {"Origin2"}, {{"Count", each {1..Table.RowCount(_)}}}),
    expand = Table.ExpandListColumn(group, "Count")
in
    expand
  • TonyHansson's avatar
    TonyHansson
    Frequent Visitor
    2 years ago

     

    = Table.AddColumn(#"Removed Columns4", "Custom",  let source = SAPIMPORT,
    group = Table.Group(source, {"Origin2"}, {{"Count",each {1..Table.RowCount(_)}}}),
    expand = Table.ExpandListColumn(group, "Count")
    in expand)

     

    Expression.Error: The column 'Origin2' of the table wasn't found.
    Details:
    Origin2

     

    Origin2 is not a part of the table "SAPimport" but created later

     

    Tony

     

    • AlienSx's avatar
      AlienSx
      Super User
      2 years ago

      You have a single column table in your original message. What is SAPimport? We are not aware of this. 

      • TonyHansson's avatar
        TonyHansson
        Frequent Visitor
        2 years ago

        Hi AlienSx 

         

        sapimport is the table, the power query is using

    • foodd's avatar
      foodd
      Community Champion
      2 years ago

      let
      Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcrEwMDBQitVBYVniZMUCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Origin2 = _t]),
      group = Table.Group(Source, {"Origin2"}, {{"Count", each {1..Table.RowCount(_)}}}),
      expand = Table.ExpandListColumn(group, "Count")
      in
      expand

       

       

       



      • TonyHansson's avatar
        TonyHansson
        Frequent Visitor
        2 years ago

        Hi foodd 

        Thank you

         

        I get the measage:

         

        Expression.Error: We cannot convert a value of type Table to type Function.
        Details:
        Value=[Table]
        Type=[Type]

    • foodd's avatar
      foodd
      Community Champion
      2 years ago

      Excel Power Query from same workbook:

      let
      Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
      group = Table.Group(Source, {"Origin2"}, {{"Count", each {1..Table.RowCount(_)}}}),
      expand = Table.ExpandListColumn(group, "Count")
      in
      expand