How to use formula in power query - excel

Question

I used formula =IF($A1<>$A2,COUNTIF(A:A,$A2),"") in excel column 'J2'. There are n rows in sheet. I want to apply this formula to all rows. In excel we do copy formula by drag and drop.

How can I achieve the same using power query.

currently I'm using power query to load external data in excel.

Any input will be helpful.. Thanks

That looks like counting unique instances of items in columnA, In powerquery, right click Column A, choose Group By ,,,, and use operation Count Rows — horseyride
@horseyride, Yes, it is counting rows. The rows(column A values) are in ascending order. The first row should only show count value. The following matching rows should be empty. I tried GroupBy and Count in power query but it show count in all matching rows... — ranjith

horseyride horseyride · Accepted Answer · 2020-07-17T14:55:11

Assuming data is Table1 with first column sorted and named Column1

Group and for the two operations, use Count Rows and All Rows

Expand

Add Index

Use index to refer to prior row

If current row's Column1 = prior row's Column1 then null otherwise the count

Remove extra columns

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"Column1"}, {{"Count", each Table.RowCount(_), type number}, {"Data", each _, type table}}),
#"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Column1"}, {"Column1.1"}),
#"Added Index" = Table.AddIndexColumn(#"Expanded Data", "Index", 0, 1),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [Index]=0 then [Count] else if #"Added Index"{[Index]} [Column1] = #"Added Index"{[Index]-1} [Column1] then null else [Count]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column1.1", "Index"})
in #"Removed Columns"

How to use formula in power query - excel

1 Answers