Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi, I need to count the amount of repeated data from the [Concatenation] column
Of that, I need to count which of them are repeated, and if they are what number it represents.
Example:
ASD
ASD
ASD
ASD
azx
azx
AJH
In this case asd is repeated 4 times, but the formula I manage to obtain gives me as a result 4.
ASD = 4
ASD = 4
ASD = 4
ASD = 4
And I want it to appear as follows:
ASD = 1
ASD = 2
ASD = 3
ASD = 4
For the previous example it would therefore be:
ASD =1
asd=2
asd=3
asd=4
azx=1
azx=2
ajh=1
That should be as a column with formula and not as a calculated measure since what I need is to consider only those data that result in the number 1 and the rest I do not have to consider.
Thank you.
Solved! Go to Solution.
Hi,
This M code works
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Grouped Rows" = Table.ExpandTableColumn(Table.Group(Source, {"Text"}, {{"All", each Table.AddIndexColumn(_,"Count",1), type table}}), "All", {"Count"})
in
#"Grouped Rows"
Hope this helps.
Hi,
This M code works
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Grouped Rows" = Table.ExpandTableColumn(Table.Group(Source, {"Text"}, {{"All", each Table.AddIndexColumn(_,"Count",1), type table}}), "All", {"Count"})
in
#"Grouped Rows"
Hope this helps.
It doesn't work for me. Is there a video to see what the procedure is like?
Could you share a screenshot of yout table so I can guide you?
Hi
1. Add an index column to the table using Powerquery
2. Add a calculated column using this DAX code
New Column= CALCULATE( COUNTROWS('Table'), FILTER( 'Table', EARLIER( 'Table'[Concatenation]) = 'Table'[Concatenation] && 'Table'[Index] <= EARLIER( 'Table'[Index]) ))
Did it work ? 👌 Mark it as a solution to help spreading knowledge 👉 A kudos would be appreciated