Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi everyone,
My question may be simple but would appreciate any reply.
I am tryng to count the number of rows equal to the "current row" but i think i am missing how to specify the current row since the last row should be 0 as there is no similar values in the column.
"Group by" is not an option since i am builiding up the formula to cover multiple columns.
Column2 | C1 |
A0.002 | 5 |
A0.002 | 5 |
A0.002 | 5 |
A0.002 | 5 |
A0.002 | 5 |
A0.021 | 5 |
here is the power query:
let Source = Csv.Document(File.Contents("C:\Users\xx\Downloads\csv.csv"),[Delimiter=",", Columns=6, Encoding=1252, QuoteStyle=QuoteStyle.None]), #"CT" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}}), #"Added C1" = Table.AddColumn(CT,"C1",each Table.RowCount(Table.SelectRows(CT, each ([Column2]="A0.002")))) in #"Added C1"
Hi,
This works
let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column2", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Column2"}, {{"Count", each Table.RowCount(_), type number}, {"All", each _, type table}}), #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Column2"}, {"Column2.1"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded All",{"Column2.1"}) in #"Removed Columns"
Hope this helps.
Thanks @Ashish_Mathur ,
i think really i should really used the "group by" but my overall data is like this -
i need to do the following
1. count the number of repeats in eah row against its own column - you have provided that. thank you
2. count the number of repeats of each row against the rest of the columns - can this be done?
3. last is to create a new column to count the number of rows where there are only of the from the first column match to any other rows.
thanks for any help
Column1 | Column2 | Column3 | Column4 | Column5 | Column6 |
A0.001 | A0.002 | A0.003 | A0.004 | A0.005 | A0.006 |
A0.001 | A0.002 | A0.003 | A0.004 | A0.005 | A0.007 |
A0.001 | A0.002 | A0.003 | A0.004 | A0.005 | A0.008 |
A0.001 | A0.002 | A0.003 | A0.004 | A0.005 | A0.009 |
A0.001 | A0.002 | A0.003 | A0.004 | A0.005 | A0.01 |
A0.001 | A0.021 | A0.022 | A0.023 | A0.024 | A0.025 |
Hi,
I do not understand your requirement. Show your expected result.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |