Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

How to add a column which counts up the repeating value in another column?

Hi,   i would like to add a column in my dataset which counts up the repeating invoice numbers. Result should look like this:    Invoice no. Count up 1234 1 1234 2 1234 3 1234 ...
Need Help
BA_Pete's avatar
BA_Pete
Icon for Super User rankSuper User
4 years ago

Hi Anonymous ,

 

Paste this into a new blank query using Advanced Editor. It gives you both a total count and running count:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlHSUTJUitWBc4yQOcbIHBNkjimYA2Sbwg2AcoyQORADgGwzuDIoxwiZg6IMaE8sAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Invoice no." = _t, #"Count up" = _t]),
    groupInvNo = Table.Group(Source, {"Invoice no."}, {{"Count", each Table.RowCount(_), Int64.Type}, {"data", each _, type table [#"Invoice no."=nullable text, Count up=nullable text]}}),
    addNestedIndex = Table.TransformColumns(groupInvNo, {"data", each Table.AddIndexColumn(_, "Index", 1, 1)}),
    expandIndex = Table.ExpandTableColumn(addNestedIndex, "data", {"Index"}, {"Index"})
in
    expandIndex

 

 

 

Pete

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Pete,

     

    thank you for the quick response. I think thats generally the right thing, but using your skript its not adding the colums to my existing query. Whats the formula to just add the column "Index" to my existing data? I cant figure it out.

     

     

    • BA_Pete's avatar
      BA_Pete
      Icon for Super User rankSuper User
      4 years ago

      Hi Anonymous ,

       

      It's not a formula itself, you need to group your data on [Invoice No] first, with an All Rows aggregation column, add the index, then expand the index back out again.

      It's not something you can do with just a calculated column as Power Query has no concept of the separate invoice numbers until you group on them.

       

      This step creates the group with an All Rows aggregated column (called [data]) and a count column ([Count]):

      groupInvNo = Table.Group(Source, {"Invoice no."}, {{"Count", each Table.RowCount(_), Int64.Type}, {"data", each _, type table [#"Invoice no."=nullable text, Count up=nullable text]}}),

       

      Then, this step adds an index column ([Index]) to the nested tables within the All Rows group column:

      addNestedIndex = Table.TransformColumns(groupInvNo, {"data", each Table.AddIndexColumn(_, "Index", 1, 1)}),

       

      Finally, this step expands the index ([Index]) back out to reinstate all your original rows:

      expandIndex = Table.ExpandTableColumn(addNestedIndex, "data", {"Index"}, {"Index"})

       

      Pete

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Sorry, for late reaction. Was to busy with other topics to work on with this in the last days. Tried now your first step in the advanced editor. As its in german I had to change "source" to "quelle". But it says it cant find the column "Invoice number". I have checked it several times. Its written correctly. 

         

         

        I guess it has something to do with the way how the Data is imported:

        Its expanding the table just after the second step:

        What do I have to write instead of "source".

        Sorry, but i am a complete newbie here.

         

        Thx in advance