Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
101 | |
78 | |
75 | |
49 |
User | Count |
---|---|
145 | |
108 | |
107 | |
89 | |
61 |