cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
theo Helper II
Helper II

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
Super User IV
Super User IV

Re: how to refer to current row in Power query

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/
theo Helper II
Helper II

Re: how to refer to current row in Power query

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

 

Super User IV
Super User IV

Re: how to refer to current row in Power query

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
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors