Realizing countif in PowerQuery

[skean21]

Dear community,

I would like to realize the following excel functions in PowerQuery:

countif($C$2:C2;C2)

--> this function gives as result, for at what place a value appears in column C (before I sorted the table after column C)

example: 

column C	result countif
A	1
A	2
B	1

the secound function I would like to realize refers to the result of the countif function.
It shows at whether a value in column C appears for the last time. 
if the result of countif is in column L, the function would look like this:

if((L2-L3)<0;0;1)

example:

column C	result countif	result last time?
A	1	0
A	2	1
B	1	1

Could anyone tell me how to put this in a M language expression? I've been searching without success so far.

Thanks and regards

Felix

[Jimmy801 · Accepted solution]

Hello @skean21 

you can add a column using this Syntax

List.Count(List.Select(ChangedType[Column1] , (listitem)=> listitem=[Column1]))

Here the complete scenario

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1UElnZBIZ6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(ChangedType, "CountIf", each List.Count(List.Select(ChangedType[Column1] , (listitem)=> listitem=[Column1])))
in
    #"Added Custom"

Copy paste this code to the advanced editor to see how the solution works

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy

[dax · Accepted solution]

Hi skean21, 

It seems that you want to mark the last time of characters' position, right? If so, you could refer to below M code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1YGRTkikG5h0AZOuYDIYSWUYmHTGUO8I0xULAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"name", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Grouped Rows" = Table.Group(#"Added Index", {"name"}, {{"MAX", each List.Max([Index]), type number}, {"ALL", each _, type table [name=text, Index=number]}}),
    #"Expanded ALL" = Table.ExpandTableColumn(#"Grouped Rows", "ALL", {"Index"}, {"Index"}),
    Custom1 = Table.ReplaceValue(#"Expanded ALL", each [MAX], each if [MAX]=[Index] then 1 else 0, Replacer.ReplaceValue, {"MAX"}),
    #"Sorted Rows" = Table.Sort(Custom1,{{"Index", Order.Ascending}})
in
    #"Sorted Rows"

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jimmy801]

Hello @skean21 

were you able to solve the problem with any reply given?

If so, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

All the best

Jimmy

[dax]

Hi skean21,

Could you please tell us if your question has been resolved. If so, in order to close the thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[skean21]

Dear community,

thanks a lot for your help so far and sorry for answering late - I didn't get really try this out until just now.

I have tried all the suggested solutions - it works for identifying the last place a value in column1 appears in my table.

(with the Max-function)

However, I also need to know the first place a value appears. That's why I used the countif-function.

@AnonymousI've tried your solution including countif, but it was too CPU intense. My data table is a CSV-File with 200MB and 180.000 rows. The calculation runs for hours without finishing.

@Anonymous @dax @Mariusz @Jimmy801 

Maybe the first place question could be solved by a combination of index and min-function?
I tried building it myself without success. Any idea?

Thanks again for your help

[Anonymous]

Hi @skean21 ,

I thought that it would be quite CPU demanding...

For completeness, this is a less compute-intense version of countif in case anyone will need it. It breaks the original sequence of rows, but it is relatively easy to fix by adding an index column before grouping and then sorting by the index after grouping (in a fashion similar to demostrated in @Jimmy801 's solution):

let
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJUitWBsIzBLCcgyxTOMgez3IAsSzDLBaQDosUVxISoDAYyjQwQBkFEw4BMEwjTGWSoGcJUC4SxCG2GRsZwO4yMTZRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [name = _t, Data = _t]),
  #"Grouped rows" = Table.Group(Source, {"name"}, {{"Index", each Table.AddIndexColumn(_, "Index", 1, 1)}}),
  #"Expanded Index" = Table.ExpandTableColumn(#"Grouped rows", "Index", {"Data", "Index"}, {"Index.Data", "Index.Index"})
in
  #"Expanded Index"

This is an alternative solution to what you want to achieve, using LIst.First / LIst.Last functionality. I've added an extra column (Data) to demonstrate packing/unpacking data in rows incase you need to select/filter the entire row, not just one column.

let
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJUitWBsIzBLCcgyxTOMgez3IAsSzDLBaQDosUVxISoDAYyjQwQBkFEw4BMEwjTGWSoGcJUC4SxCG2GRsZwO4yMTZRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [name = _t, Data = _t]),
  PackDataIntoOneColumn = Table.AddColumn(Source, "Custom", each _),
  #"Grouped First" = Table.Group(PackDataIntoOneColumn, {"name"}, {{"Data", each List.First([Custom])}, {"Type", each "First", type text}}),
  #"Grouped Last" = Table.Group(PackDataIntoOneColumn, {"name"}, {{"Data", each List.Last([Custom])}, {"Type", each "Last", type text}}),
  Custom = Table.Combine({#"Grouped First", #"Grouped Last"}),
  #"Expanded Data" = Table.ExpandRecordColumn(Custom, "Data", {"name", "Data"}, {"Data.name", "Data.Data"})
in
  #"Expanded Data"

Kind regards,

JB

[Jimmy801 · Accepted solution]

Hello @skean21 

so I missunderstood your requirement. Here the updated version of my solution. 

let
	Source = #table 
	(
		{"Column 1"},
		{ {"A"}, {"B"}, {"A"}, {"A"}, {"B"}, {"A"}, {"C"}, {"A"} } 
	),
	AddIndex = Table.AddIndexColumn
	(
		Source,
		"IndexFirstSortOrder",
		1,
		1
	),

	#"Grouped Rows" = Table.Group
	(
		AddIndex,
		{"Column 1"},
		{{"AllRows", each _, type table [Column 1=text, Index=number, Count=number]}}
	),
	TransformAllRows = Table.TransformColumns 
	(
		#"Grouped Rows",
		{ { "AllRows", (tableint)=> Table.AddIndexColumn (tableint,"SumIf",1,1)} } 
	),
	Expand = Table.ExpandTableColumn
	(
		TransformAllRows,
		"AllRows",
		{"IndexFirstSortOrder", "SumIf"}
	),
	SortOldSortOrder = Table.Sort
	(
		Expand,
		{{"IndexFirstSortOrder", Order.Ascending}}
	),
	DeleteIndex = Table.RemoveColumns
	(
		SortOldSortOrder,
		{"IndexFirstSortOrder"}
	)
 in 
	DeleteIndex

Would be interesting to see if this solution would work on a huge database like yours. Tried to place some Table.Buffer to speed it up.

Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Mariusz]

Hi @skean21 

Please see the attached file with a solution.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

[Jimmy801]

Hello @skean21 

i gave the whole thing a test. The only accetable way of doing is to use a group with a count and then to join the result to the orignal table. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1UElnZBIZ6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    Group = Table.Group(Source ,{"Column1"}, {{"Anzahl", each Table.RowCount(_), type number}}),
    Join = Table.NestedJoin(Source , "Column1", Group, "Column1","CountIf"),
    Expand = Table.ExpandTableColumn(Join, "CountIf", {"Anzahl"}, {"Anzahl"})
in
    Expand

tried to apply this on a database of 100k rows it runs 2 seconds. So this would be solution to choose

Copy paste this code to the advanced editor to see how the solution works

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy

[dax · Accepted solution]

Hi skean21, 

It seems that you want to mark the last time of characters' position, right? If so, you could refer to below M code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1YGRTkikG5h0AZOuYDIYSWUYmHTGUO8I0xULAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"name", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Grouped Rows" = Table.Group(#"Added Index", {"name"}, {{"MAX", each List.Max([Index]), type number}, {"ALL", each _, type table [name=text, Index=number]}}),
    #"Expanded ALL" = Table.ExpandTableColumn(#"Grouped Rows", "ALL", {"Index"}, {"Index"}),
    Custom1 = Table.ReplaceValue(#"Expanded ALL", each [MAX], each if [MAX]=[Index] then 1 else 0, Replacer.ReplaceValue, {"MAX"}),
    #"Sorted Rows" = Table.Sort(Custom1,{{"Index", Order.Ascending}})
in
    #"Sorted Rows"

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @skean21 

This is the full version, including countif:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1UElnZBIZ6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Index" = Table.AddIndexColumn(ChangedType, "Index", 0, 1),
    CountIf = Table.AddColumn(#"Added Index", "CountIf", (r)=> Table.RowCount (Table.SelectRows(#"Added Index", each [Index] <= r[Index] and [Column1]=r[Column1])), type number),
    #"Grouped Rows" = Table.Group(CountIf, {"Column1"}, {{"MaxIndex", each List.Max([Index]), type number}}),
    #"Merged Queries" = Table.NestedJoin(CountIf, {"Index"}, #"Grouped Rows", {"MaxIndex"}, "Grouped Rows", JoinKind.LeftOuter),
    result = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"MaxIndex"}, {"MaxIndex"})
in
    result

However, countif is quite CPU-greedy and may have a severe impact on performance, especially on large sets of data. If the only reason for having it is the second step - where you find last entry for each [Column1], you can just remove the CountIf step the code will run without it:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1UElnZBIZ6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Index" = Table.AddIndexColumn(ChangedType, "Index", 0, 1),
    #"Grouped Rows" = Table.Group(#"Added Index", {"Column1"}, {{"MaxIndex", each List.Last([Index]), type number}}),
    #"Merged Queries" = Table.NestedJoin(#"Added Index", {"Index"}, #"Grouped Rows", {"MaxIndex"}, "Grouped Rows", JoinKind.LeftOuter),
    result = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"MaxIndex"}, {"MaxIndex"})
in
    result

Kind regards,

JB

[Jimmy801 · Accepted solution]

Hello @skean21 

you can add a column using this Syntax

List.Count(List.Select(ChangedType[Column1] , (listitem)=> listitem=[Column1]))

Here the complete scenario

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1UElnZBIZ6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(ChangedType, "CountIf", each List.Count(List.Select(ChangedType[Column1] , (listitem)=> listitem=[Column1])))
in
    #"Added Custom"

Copy paste this code to the advanced editor to see how the solution works

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy