countif formula in power query

Question

I had a formula in a table in excel

=IF([@STATUS]="",[KEY]&"_"&COUNTIF(INDEX([KEY],1):[@KEY],[@KEY]),"")

which showed me how often a value showed in the data. But the same is not working in Power Query

with the formula I use to get if the same value's position in a long data list, and then I use the same in index match formula to find and locate other relevant data

I am trying to achieve: Date Name Frequency 1/10/2019 Adrian Bartholomeusz 1 1/10/2019 Aditya Tipnis 1 2/10/2019 Abdul Atef 1
2/10/2019 Aditya Tipnis 2 3/10/2019 Abdul Atef 2

In excel I used the formula "=COUNTIF(INDEX([Name],1):[@Name],[@Name])" but when I use the same in Power Query I am getting error

Why do you want to do that in Power Query? What is the bigger picture? Maybe there's another approach to get to the same insight. — teylyn
I got data in number of files.. and I want a dashboard in a macro file.. as I get the data from number of files to the dashboard, excel is making me use Power Query to link data.. once the data is linked it becomes a table and I have to add a new column with formula to get the information I need... Plus I can't add the column in raw data files, as they are just downloaded from number of different systems and want users to not worry about formula's and stuff when they get the raw data.. hope I was able to explain my dilemma. — Saurabh Agrawal
It would be best to show an actual sample of data and what you are trying to accomplish. Many Excel formulas (and their methodology) do not translate directly to Power Query because they operate in very different ways. However there is usually a way to accomplish the same goal in Power Query utilizing it's own tools. — Wedge
I am trying to achieve information as shown in Column1 Date Name Column1 1/10/2019 Adrian Bartholomeusz 1 1/10/2019 Aditya Tipnis 1 2/10/2019 Abdul Atef 1 2/10/2019 Aditya Tipnis 2 3/10/2019 Abdul Atef 2 In excel I used the formula "=COUNTIF(INDEX([Name],1):[@Name],[@Name])" but when I use the same in Power Query I am getting error — Saurabh Agrawal
Please edit your question to add clarification. Do not do that in comments. Nobody wants to read through a wall of comments to understand what you are actually asking. Edit your question and post a comment when you're done, so people who follow this question are notified. — teylyn

Rosetta Rosetta · Accepted Answer · 2019-10-12T19:42:51

The key steps are:

Add Index
Group Rows
Transform Columns to add a sub-index.
Expand the data back.

The rest are cosmetics.

let
    Source = Excel.CurrentWorkbook(),
    Table1 = Source{[Name="Table1"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Table1, "Index", 0, 1),
    #"Grouped Rows" = Table.Group(#"Added Index", {"key"}, {{"Data", each _, type table [key=number, f=text, Index=number]}}),
    #"TransformColumns" = Table.TransformColumns(#"Grouped Rows",{"Data", (x) => Table.AddIndexColumn(x, "Index2", 1, 1)}),
    #"Expanded Data" = Table.ExpandTableColumn(#"TransformColumns", "Data", {"excel formula", "Index", "Index2"}, {"excel formula", "Index", "Index2"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Data", "PQ method", each Text.From([key]) & "_" & Text.From([Index2])),
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index", "Index2"})
in
    #"Removed Columns"

countif formula in power query

1 Answers