CountIF/number of occurences column in PowerQuery

Question

I am trying to transition a process from Excel to PowerQuery. What I need to do is to create a separate column which list how many times serial number occures in the column.

I have already tried two solutions:

• GroupBy - It works but on smaller datasets. I need to keep remaining data (30 columns) so that I run our of memory
• also tried list function but it just keeps on loading for a half an hour or so

    List.Count(List.FindText(Source[Column1], [Column2]))    

Can you help me to figure out another solution for this? I am using MS Excel 2016, have 8GB RAM.

Answer 1

One way is adding custom column.

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Custom" = Table.AddColumn(Source,"Count",(i)=>Table.RowCount(Table.SelectRows(Source, each [serial]=i[serial])), type number )
in #"Added Custom"

One way is to group and count, then expand

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"serial"}, {{"Count", each Table.RowCount(_), type number}, {"All", each _, type table}}),
ExpandList = List.Difference(Table.ColumnNames(Source),{"serial"}),
#"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", ExpandList, ExpandList)
in  #"Expanded All"