Forum Discussion

Anonymous

Not applicable

6 years ago

Solved

Realizing countif in PowerQuery

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

Need Help

Jimmy801

6 years ago

Hello Anonymous

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

6 years ago

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

6 years ago

Hello Anonymous

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

10 Replies

Anonymous

Not applicable

6 years ago

Hi Anonymous

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,

Jimmy801

Community Champion

6 years ago

Hello Anonymous

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

Community Support

6 years ago

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

Community Champion

6 years ago

Hello Anonymous

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

Mariusz
Community Champion
6 years ago
Hi Anonymous

Please see the attached file with a solution.

Best Regards,
Mariusz

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

Realizing countif in PowerQuery.pbix22 KB
dax
Community Support
6 years ago
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.
- Anonymous
  Not applicable
  6 years ago
  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
  - Jimmy801
    Community Champion
    6 years ago
    Hello Anonymous
    
    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
Jimmy801
Community Champion
6 years ago
Hello Anonymous

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

Forum Discussion

Realizing countif in PowerQuery

10 Replies

Related content

PowerQuery Countifs across multiple columns

Countif (or equivalent) Per Row in PowerQuery/Dax

PowerQuery transformation

Index Countif

Countifs