Rank Occurence of text in new column in Power Query (M)

[GiaPa]

Hi all,

I have a very simple request for which I cannot find the answer.

I have the table below. I need a new column that counts the number of times the text in the domain column appears. All domains will appear twice max. So instead of the index nr I need instead number 1 for rows 1 to 8 and 2 for rows 9 to 16. However I don't want these values to be fixed on the index number but dependent on the domain name only (because there might be new entries in the model). How can I do that?

So Wholesale and Rural should be 1 in row 1 and 2 in row 9. Similary for the rest.

[ronrsnfld]

Here  is a method using List.Generate to create and Running total of the count.

Given this data source:

Run this code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs/Iz0ktTsxJVUjMS1EIKi1KzFGK1YlWcnZzdfaHsDxDIAyPIAg/v6ggvyixJBXCC4KqcoPQ/mWpRZnpYCb1jA5ANTsWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [swp_domainname = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"swp_domainname", type text}}),
    
    #"Add Running Count" = Table.FromColumns(
        Table.ToColumns(#"Changed Type") &
        {List.Generate(
        ()=>[c=1, idx=0],
        each [idx]<Table.RowCount(#"Changed Type"),
        each [c=let 
            currList = List.Buffer(List.Range(Table.Column(#"Changed Type","swp_domainname"),0,[idx]+2)),
            selItems = List.Select(currList, (cl)=> cl = currList{[idx]+1})
            in List.Count(selItems), idx=[idx]+1],
        each [c])}, type table[swp_domainname=text, Count=Int64.Type])
in
    #"Add Running Count"

to produce:

If you have other columns, you may need to add them to the "Add Running Count" step table type.

[GiaPa]

Thank you Ron. Your formula helped me take the next step although it resulted in a table than a list. This probably means it's not 100% correct and since I'm really bad with M I wasn't able to adjust it.

I expanded the two columns and then created a T/F to keep records where domain name = domain name from running count

This created 16 rows per row so I had to do some matching, filtering and removing duplicates but in the end I was able to get to what I needed:

Can you explain the code below? I need to reuse the formula in another table for subdomain. I assume this value should change?

Binary.FromText("i45WCs/Iz0ktTsxJVUjMS1EIKi1KzFGK1YlWcnZzdfaHsDxDIAyPIAg/v6ggvyixJBXCC4KqcoPQ/mWpRZnpYCb1jA5ANTsWAA==", BinaryEncoding.Base64

I'm afraid your formula is specifically made for these values but I need it to be dynamic. So if there are new values it won't work.

[ronrsnfld]

If you use it as it was designed, it produces a table with the added column, as you can clearly see from the screenshots I posted. And it will adapt to what ever you have in the "swp_domaine" column. New values will be utilized. It will not produce a column of tables.

I don't understand why you embedded it in a Table.AddColumns function when I showed no such thing in my code.  But that is clearly why you are getting unusable results.

All you need to do is insert the part of the code after #"Changed Step" at the point where you have a table that includes the column to which you want to apply the running count. You may have to adjust the table reference from #"Changed Step" to whatever the name of that step is in your code.

If you examine the code, you will see that the "Source" and #"Changed Step" steps are merely to give the rest of the code something to work with. But the code can work with whatever you use to produce the table with the column of text strings to which you want to apply a running count.

[GiaPa]

I did not change the code; it was changed automatically when I pasted it. I used add column function cause I didn't know I can add a step and code there. I got some help from a colleague and now your formula works. We just had to make some changes. Thanks a lot!

[ronrsnfld · Accepted solution]

If that is the case, then it appears that you incorrectly pasted all of the code into the "Add Column" dialog, instead of the relevant part of the code into the Advanced Editor.

I suggest you try to follow these instructions step-by-step and let me know the results.

If it doesn't work, post your code here as copied from the Advanced Editor along with any error messages.

• Create a new Blank query
• Open the Advanced Editor (On the Home menu option)
• Replace the contents of what you see with the code I posted.
• Explore the Applied Steps to see what is going on at each step.

Then, in a copy of your own query:

• Remove steps so that the last step shows the column you are interested in.
• Open the Advanced Editor.
• Replace your code starting with "in" (probably the penultimate line) with the relevant code from my answer.
  • The relevant code will be the part of the code after the #"Changed Type" step
  • Change the table references in my code which you just pasted (#"Changed Type") to whatever the name of the preceding step is in your code. (It may be the same or it may be different)

[GiaPa]

I updated my reply but apparetly you received the first one. Indeed I removed the json part and changed the columns references and it worked. Thanks!

[GiaPa]

First of all thanks for the quick reply!

I think I followed your advice but the result is not what I wanted. Maybe because I added the index via the add index column which added 1-8? I also didn't delete all the columns because I needed to keep the column with the table (count) otherwise I cannot expand it again. So I only deleted the domain column and expanded the count column. I also tried with using an index withou incrementing which resulted in all values being 1 before and after the expansion.

[Papermain]

You can apply group by based on domain name and instead of selecting count operator, you select "all" . Then it will create a nested table. Now create a new custom column to add an index on those nested tables:

Table.AddIndexColumn([all],"Count", 1)

Now delete all column except for the new one and expand the data.

Full code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column1"}, {{"all", each _, type table [Column1=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Sequence", each Table.AddIndexColumn([all],"Count", 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column1", "all"}),
    #"Expanded Sequence" = Table.ExpandTableColumn(#"Removed Columns", "Sequence", {"Column1", "Count"}, {"Column1", "Count"})
in
    #"Expanded Sequence"

Before/After

https://imgur.com/a/WG0yyUL