Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello, I have a numeric table, and I need to determine how many times the value occurs in the table and list it on the New Column
Ex.
catalog | result |
1 | 2 |
8000 | 3 |
2 | 1 |
3 | 1 |
1 | 2 |
8000 | 3 |
8000 | 3 |
can you please advise what the formula in Power Query Editor (M)?
thank you answer
Solved! Go to Solution.
Hi there,
you can use "Earlier" in M as well - this enables you to consolidate @tringuyenminh92 's steps into one line of code like this:
result = Table.AddColumn(Source, "Custom", (Earlier) => List.Count(List.Select(Source[catalog], each _=Earlier[catalog])))
Where "Source" is the reference to your table.
Technical details, if of interest:
It does very much the same like the DAX-function:
Source refers to the whole table like All(Data)
[catalog], each _ introduces the column "catalog" as the object to work on like Data), Data[Catalog]
=Earlier[catalog] reads almost the same like in DAX: =EARLIER(Data[Catalog])
Just that in M, you can replace the string "Earlier" by anything you want. It doesn't have a meaning, but just makes sure that the inner-context (which you adress by using the underscore "_") has a different adress than the outer one 🙂
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi @skopcak,
If you still need creating couting column by power query (M), please check my sample:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAeJYnWglCwMDAyDHCMwxArKMwSxjIMsEzAIpNUVWaobMMVeKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Catalog = _t, No = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Catalog", type text}, {"No", Int64.Type}}), #"TableBuffer" = Table.Buffer(#"Changed Type") in #"TableBuffer"
(item) => let #"ListCatalog" = Table.ToList(Table.SelectColumns(BufferOfData,"Catalog")), result = List.Count(List.Select(#"ListCatalog", each _ = item)) in result
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAeJYnWglCwMDAyDHCMwxArKMwSxjIMsEzAIpNUVWaobMMVeKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Catalog = _t, No = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Catalog", type text}, {"No", Int64.Type}}), #"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "Result", each CountItem([Catalog])) in #"Invoked Custom Function"
Hello,
my source data comes from
let
Source = Excel.CurrentWorkbook(){[Name="Tabulka1"]}[Content]
You can advise how to change the source
your code
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAeJYnWglCwMDAyDHCMwxArKMwSxjIMsEzAIpNUVWaobMMVeKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Catalog = _t, No = _t]),
thank you answer
Hi there,
you can use "Earlier" in M as well - this enables you to consolidate @tringuyenminh92 's steps into one line of code like this:
result = Table.AddColumn(Source, "Custom", (Earlier) => List.Count(List.Select(Source[catalog], each _=Earlier[catalog])))
Where "Source" is the reference to your table.
Technical details, if of interest:
It does very much the same like the DAX-function:
Source refers to the whole table like All(Data)
[catalog], each _ introduces the column "catalog" as the object to work on like Data), Data[Catalog]
=Earlier[catalog] reads almost the same like in DAX: =EARLIER(Data[Catalog])
Just that in M, you can replace the string "Earlier" by anything you want. It doesn't have a meaning, but just makes sure that the inner-context (which you adress by using the underscore "_") has a different adress than the outer one 🙂
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi @skopcak,
Could I achieve it by DAX? Create calculated column
result = CALCULATE(COUNTA(Data[Catalog]),FILTER(ALL(Data),Data[Catalog]=EARLIER(Data[Catalog])))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |