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

 

LOL! Ok, this is what we call being at the wrong end of an XY Problem.

I take the blame, I should've asked what you were ACTUALLY trying to do 🙂

 

Check this out:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JcvBCQAgDEPRXXoWmkYrOIt0/zWMFHJ5fHKvIVwjSBvG5ZiNGmp0bDGOGrJBdNPvNNX0y4+wqgc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Planned start date" = _t, #"Planned finish date" = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Planned start date", type date}, {"Planned finish date", type date}}),
    
    addMonthList =
    Table.AddColumn(chgTypes, "monthList", each
        List.Distinct(
            List.Transform(
                {Number.From([Planned start date]).. Number.From([Planned finish date])},
                each Date.EndOfMonth(Date.From(_))
            )
        )
    ),
    expandMonthList = Table.ExpandListColumn(addMonthList, "monthList")
in
    expandMonthList

 

The 'addMonthList' step splits your contract ranges into monthly rows which you can relate to your calendar.

 

Pete

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi Pete,

 

thank you very much for the alternative. 

But i am running into a problem with your skript with rows which have no planned start / finish date:

 

 

Can the skript be expanded, so that only the fille rows are considered?

 

Thank you in advance.

 

Cheers

Martin