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.
Hi,
Was hoping people could help. I would like to add a column in Query editor counting the number of times a value appears in a column in ascending order (see below desired output). I have tried to combine countrows, filter and earliest but haven't quite figured it out. Thanks!
Country | Value |
Algeria | 1 |
Belgium | 1 |
Belgium | 2 |
Belgium | 3 |
Canada | 1 |
Canada | 2 |
Canada | 3 |
Canada | 4 |
Chile | 1 |
Solved! Go to Solution.
Hi @AuroraNI
Add the index column first, and then add the calculated column:
Column = CALCULATE(DISTINCTCOUNT('Table'[Index]),FILTER('Table',[Country]=EARLIER('Table'[Country])&&[Index]<=EARLIER('Table'[Index])))
Pbix attached.
Hi @AuroraNI
Add the index column first, and then add the calculated column:
Column = CALCULATE(DISTINCTCOUNT('Table'[Index]),FILTER('Table',[Country]=EARLIER('Table'[Country])&&[Index]<=EARLIER('Table'[Index])))
Pbix attached.
Thanks for this, worked really well!
Hi,
Your question is not clear. Share 2 seperate tables - input and output.
Hi, apologies here is the input column
Country |
Algeria |
Belgium |
Belgium |
Belgium |
Canada |
Canada |
Canada |
Canada |
Chile |
and here is the output I would like
Country | Value |
Algeria | 1 |
Belgium | 1 |
Belgium | 2 |
Belgium | 3 |
Canada | 1 |
Canada | 2 |
Canada | 3 |
Canada | 4 |
Chile | 1 |
Hi,
This M code works
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Country", type text}}),
Partition = Table.Group(#"Changed Type", {"Country"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
#"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Index"}, {"Index"})
in
#"Expanded Partition"
Hope this helps.
Thanks for this answer, I have tried this and works thank you. I will go with the calculated column option as simpler in my current dashboard
You are welcome.
Hi, maybe there are better ways but this is my first way:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcixKT83JTFSK1YlWci7KT0yGsgNSi0rBDGQFrq6hoWCGb2pFZnI+qphTUWJxZg5uzXBBDFNiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Pais = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Pais", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Pais"}, {{"Count", each _, type table [Pais=text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"index",1,1)),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Pais", "index"}, {"Custom.Pais", "Custom.index"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Pais", "Count"})
in
#"Removed Columns"
Regards
Victor
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 |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |