CODEWITHSUNDEEP
excelpowerquery
ranjith
ranjith

Reputation: 209

How to use formula in power query - excel

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

Upvotes: 1

Views: 277

Answers (1)

horseyride
horseyride

Reputation: 21393

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"

Upvotes: 1

Related Questions