Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I am trying to count the occurence number of a value using DAX. The output needed is as follows:
Input Output
A 1
A 2
B 1
C 1
D 1
B 2
I found a way to do this is Power Query using the following formula for a new column:
= Table.AddColumn(#"Added Index", "Output", each Number.Abs(List.Count(List.RemoveItems(List.Range(#"Added Index"[Tracking Number], 0, [Index]), {[Tracking Number]}))-List.Count(List.Range(#"Added Index"[Tracking Number], 0, [Index]))))
The problem with this is that it takes forever to load values in the new column and I only once got an output after hours of waiting.
Any help on this would be appreciated. Thanks in advance.
Solved! Go to Solution.
Hi @Anonymous
I believe the sort order within the group should be preserved but let me know if it's not.
See the below, revised to accommodate other columns.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1YGRTmDSGUy6wERiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Input = _t]),
#"Grouped Rows" = Table.Group(Source, {"Input"}, {{"tbl", each _, type table [Input=text]}}),
#"Add Index to tbl" = Table.TransformColumns( #"Grouped Rows", {{ "tbl", each Table.AddIndexColumn( _, "Index", 1, 1 ) , type table [Input=text, Index=number] }} )
in
#"Add Index to tbl"
Hi @Anonymous
Give this a go.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1YGRTmDSGUy6wERiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Input = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Input", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Input"}, {{"Output", each Table.AddIndexColumn( _, "Output", 1, 1 )[Output], type list }}),
#"Expanded Output" = Table.ExpandListColumn(#"Grouped Rows", "Output"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Output",{{"Output", Int64.Type}})
in
#"Changed Type1"
Thank you for your quick response @Mariusz . Your solution seems to work but I have other columns in this table and they disappear when I use your code. I guess I should have framed my question a little better.
There are other columns as well in this table that are needed in the final output. Also, grouping the column changes the way the data was sorted. When grouping, does the original order in which a particular value appear stay intact or are the values sorted again?
I can check this if I can keep index column in the output but I need a solution that lets me keep all the original columns as well.
Hi @Anonymous
I believe the sort order within the group should be preserved but let me know if it's not.
See the below, revised to accommodate other columns.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1YGRTmDSGUy6wERiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Input = _t]),
#"Grouped Rows" = Table.Group(Source, {"Input"}, {{"tbl", each _, type table [Input=text]}}),
#"Add Index to tbl" = Table.TransformColumns( #"Grouped Rows", {{ "tbl", each Table.AddIndexColumn( _, "Index", 1, 1 ) , type table [Input=text, Index=number] }} )
in
#"Add Index to tbl"
@Mariusz I figured out where I was going wrong and your solution works. Thanks for your help!
Hi @Anonymous
Happy you worked it out
Many Thanks
Mariusz
@Mariusz I tried using your suggestion but am still unable to get all the columns in final output. Instead it is just returning the Input and output columns and maybe that is because I am doing something wrong.
The last three steps before I need to perform this operation are as follows:
#"Changed Type3" = Table.TransformColumnTypes(#"Renamed Columns",{{"Others", type number}, {"International Freight", type number}, {"Units", type number}, {"Entry", type text}, {"Weight (Kg)", type number}, {"Pending Cases", Int64.Type}, {"No. of cases", Int64.Type},{"Arrival Date", type date}}),
#"Sorted Rows" = Table.Sort(#"Changed Type3",{{"Date", Order.Ascending}, {"FileNo", Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1)
in
#"Added Index"
I would like to see all of the columns mentioned in #"Changed Type3". The column by which we are supposed to group by here would be "Tracking Number".
Thank you for your continued support on this.
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |