Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
theo
Helper III
Helper III

how to refer to current row in Power query

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.

 

Column2C1
A0.0025
A0.0025
A0.0025
A0.0025
A0.0025
A0.0215

 

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"
3 REPLIES 3
Ashish_Mathur
Super User
Super User

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

 

Column1Column2Column3Column4Column5Column6
A0.001A0.002A0.003A0.004A0.005A0.006
A0.001A0.002A0.003A0.004A0.005A0.007
A0.001A0.002A0.003A0.004A0.005A0.008
A0.001A0.002A0.003A0.004A0.005A0.009
A0.001A0.002A0.003A0.004A0.005A0.01
A0.001A0.021A0.022A0.023A0.024A0.025

 

Hi,

I do not understand your requirement.  Show your expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.