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

[Marting86]

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	4
1234	5
2345	1
2345	2
2345	3
3456	1
3456	2
3456	3
3456	4
…	…

Thank you in advance for your support.

[BA_Pete]

Argh, you snuck your post in while I was typing mine!

Yes, should be able to expand to include certain [Item No] values, but would be easier to work on the original dataset as per my other post just now.

Pete

[Marting86]

Ok, lets do as suggested by you in your other post. I will take one of my files which is creating the error while uploading and leave in only the neccessary columns. What will be the best way to provide it to you?

[BA_Pete]

If you're happy for it to be public, I think you should be able to drag and drop it into a post on here. If that doesn't work, then you could share it from OneDrive/Google Docs/ DropBox and post the link here.

Pete

[Marting86]

Can you use that Pete? 

Tell me please when you have downloaded it, so I can take it down again.

[BA_Pete]

Okay, downloaded. You can delete post.

I'll have a look later and let you know how I get on.

Pete

[Marting86]

Thank you very much.

[BA_Pete]

Okay, so the issue was a fairly simple one: you had some dates that were zeroes:

I've added a simple replace step at the start to negate this and tidied up the code overall. You'll need to add your source steps at the top (I think everything up to #"Gefilterte Zeilen"?) but should work perfectly now.

You'll notice that I put your column rename step right at the end of the query - I always recommend to do this so that if you want to change column names in the future it doesn't break your whole query.

let
    Source = yourSourceSteps,

    repDateZeroToNull = Table.ReplaceValue( yourLastSourceStep ,0,null,Replacer.ReplaceValue,{"Invoice date", "Planned start date", "Planned finish date"}),
    chgDateTypesToText = Table.TransformColumnTypes(repDateZeroToNull,{{"Invoice date", type text}, {"Planned start date", type text}, {"Planned finish date", type text}}),
    chgDateTypesToDate = Table.TransformColumnTypes(chgDateTypesToText,{{"Invoice date", type date}, {"Planned start date", type date}, {"Planned finish date", type date}}),
    addMonthList = Table.AddColumn(chgDateTypesToDate, "monthList", each
    if [Planned start date] = null or [Planned finish date] = null then null else
        List.Distinct(
            List.Transform(
                {Number.From([Planned start date]).. Number.From([Planned finish date])},
                each Date.StartOfMonth(Date.From(_))
            )
        )
    ),
    addSplitInvoiceValue = Table.AddColumn(addMonthList, "splitInvoiceValue", each
        if [monthList] = null then null else [Line amount  local c] / List.Count([monthList])),
    expandMonthList = Table.ExpandListColumn(addSplitInvoiceValue, "monthList"),
    chgNewColTypes = Table.TransformColumnTypes(expandMonthList,{{"monthList", type date}, {"splitInvoiceValue", type number}}),
    renameCols = Table.RenameColumns(chgNewColTypes,{{"Line amount  local c", "Net Invoiced Sales"}})
in
    renameCols

Regarding limiting the item codes that the split is applied to, there's a couple of ways to go about this. It really depends whether you want the item code list hardcoded into the query, or whether you will want to easily add/remove item codes in future.

To hardcode, you would use this 'addMonthList' step:

addMonthList = Table.AddColumn(chgDateTypesToDate, "monthList", each
    if [Planned start date] = null
        or [Planned finish date] = null
        or not List.Contains({"630", "100", "620"}, [Item number]) then null else
        List.Distinct(
            List.Transform(
                {Number.From([Planned start date]).. Number.From([Planned finish date])},
                each Date.StartOfMonth(Date.From(_))
            )
        )
    ),

To be able to more easily change the values in future, you can create a separate query which is just a list of [Item number] values to include, then replace the List.Contains section above like this:

//Change this:
List.Contains({"630", "100", "620"}, [Item number])

//to this:
List.Contains( nameOfYourListQuery , [Item number])

Pete

[Marting86]

Hi Pete,

can the code be expanded, so that the steps starting from "addMonthList" only apply on rows with the following Item Numbers:

In fact I only need to do the whole splitting on these ones. Maybe the issues are cause by the others. Its worth a try.

[Marting86]

Amazing, that worked:

Thank you very much. So I try to go on with the next steps now.

[Marting86]

let
    Quelle = Excel.Workbook(File.Contents("C:\Users\gemagrz\Saved Games\Desktop\Mappe1.xlsx"), null, true),
    Tabelle1_Sheet = Quelle{[Item="Tabelle1",Kind="Sheet"]}[Data],
    #"Höher gestufte Header" = Table.PromoteHeaders(Tabelle1_Sheet, [PromoteAllScalars=true]),
    #"Geänderter Typ" = Table.TransformColumnTypes(#"Höher gestufte Header",{{"Company", Int64.Type}, {"Division", type text}, {"Facility", type text}, {"Customer order numbe", Int64.Type}, {"Delivery number", Int64.Type}, {"Order line number", Int64.Type}, {"Line suffix", Int64.Type}, {"Invoice number", Int64.Type}, {"Order date", Int64.Type}, {"Requested delivery d", Int64.Type}, {"Confirmed delivery d", Int64.Type}, {"Planned delivery dat", Int64.Type}, {"Invoice date", Int64.Type}, {"Year", Int64.Type}, {"Accounting date", Int64.Type}, {"Customer", type text}, {"Name", type text}, {"Address line 1", type text}, {"Customer group", Int64.Type}, {"Customer type", Int64.Type}, {"Statistics customer", type text}, {"Customer order type", type text}, {"Recipient agreement", type text}, {"Payer", type text}, {"BC Level 1", type text}, {"BC Level 2", type text}, {"BC Level 3", type text}, {"BC Level 4", type text}, {"Currency", type text}, {"Invoice rate", Int64.Type}, {"Currency conversion", Int64.Type}, {"Salesperson", type text}, {"District", type text}, {"Country", type text}, {"Statistics identity", type text}, {"OSBSTD.Statistics identity", type text}, {"Contact method", type text}, {"Buying pattern", type text}, {"Warehouse", type text}, {"Item number", Int64.Type}, {"Item group", Int64.Type}, {"Item type", type text}, {"Product group", type text}, {"Supplier", type text}, {"OSBSTD.Statistics identity_1", type text}, {"OSBSTD.Statistics identity_2", type text}, {"Replaced item number", type text}, {"Line type", Int64.Type}, {"MUC trigger", Int64.Type}, {"Statistical U M", type text}, {"Alternate U M", type text}, {"Sales price unit of", type text}, {"Price origin", Int64.Type}, {"Discount model", type text}, {"Discount campaign", type text}, {"OSBSTD.Discount campaign", type text}, {"OSBSTD.Discount campaign_3", type text}, {"OSBSTD.Discount campaign_4", type text}, {"OSBSTD.Discount campaign_5", type text}, {"OSBSTD.Discount campaign_6", type text}, {"Price adjustment lin", Int64.Type}, {"Standard cost code", Int64.Type}, {"Contribution margin", Int64.Type}, {"Sales campaign", type text}, {"Product variant", type text}, {"Configuration number", Int64.Type}, {"Customer order categ", Int64.Type}, {"Address number", type text}, {"Future rate agreemen", type text}, {"Blanket agreement nu", type text}, {"Delivery schedule", type text}, {"Origin", Int64.Type}, {"Identity type", Int64.Type}, {"Assignment", Int64.Type}, {"Assignment suffix", Int64.Type}, {"Service expense type", Int64.Type}, {"Individual item numb", Int64.Type}, {"Item number  individ", type text}, {"Individual item grou", type text}, {"Service manager", type text}, {"Technician", type text}, {"Error symptom", type text}, {"Error cause", type text}, {"Error class", type text}, {"Action", type text}, {"Action class", type text}, {"Agreement number", type text}, {"Agreement type", Int64.Type}, {"Equipment", type text}, {"Main individual item", type text}, {"Transaction reason", type text}, {"Project number", type text}, {"Project element", type text}, {"Invoiced quantity  b", Int64.Type}, {"Invoiced quantity  s", Int64.Type}, {"Invoiced quantity  a", Int64.Type}, {"OSBSTD.Invoiced quantity  s", Int64.Type}, {"Ordered quantity  ba", Int64.Type}, {"Ordered quantity  sa", Int64.Type}, {"Ordered quantity  al", Int64.Type}, {"Ordered quantity  st", Int64.Type}, {"Gross weight", Int64.Type}, {"Net weight", Int64.Type}, {"Volume", Int64.Type}, {"Line amount  local c", type number}, {"Amount based on sale", Int64.Type}, {"Foreign currency amo", type number}, {"Discount amount 1", Int64.Type}, {"Discount amount 2", Int64.Type}, {"Discount amount 3", Int64.Type}, {"Discount amount 4", Int64.Type}, {"Discount amount 5", Int64.Type}, {"Discount amount 6", Int64.Type}, {"Total discount amoun", Int64.Type}, {"Cost price", Int64.Type}, {"Issued cost amount", Int64.Type}, {"Manufactoring cost a", Int64.Type}, {"Net cost 1", Int64.Type}, {"Net cost 2", Int64.Type}, {"Net cost 3", Int64.Type}, {"Net cost 4", Int64.Type}, {"Net cost 5", Int64.Type}, {"Reserved bonus commi", Int64.Type}, {"Days betw actual pla", Int64.Type}, {"Days between actual", Int64.Type}, {"OSBSTD.Days between actual", Int64.Type}, {"Days between conf an", Int64.Type}, {"Days between deliver", Int64.Type}, {"Number of delivery l", Int64.Type}, {"Number of order line", Int64.Type}, {"Lines with correct q", Int64.Type}, {"OSBSTD.Lines with correct q", Int64.Type}, {"Lines on time  wrong", Int64.Type}, {"Number of lines with", Int64.Type}, {"Number of manually p", Int64.Type}, {"Number of bonus gene", Int64.Type}, {"Number of bonus payi", Int64.Type}, {"Number of commission", Int64.Type}, {"OSBSTD.Number of commission", Int64.Type}, {"Invoice recipient", type text}, {"OSBSTD.Item group", type text}, {"Function area", type text}, {"Assignment type", type text}, {"Main product", type text}, {"Sales department", type text}, {"Business area", type text}, {"Product structure ty", type text}, {"Service", type text}, {"Route", type text}, {"Route departure", Int64.Type}, {"Demand", Int64.Type}, {"Attribute value", type text}, {"OSBSTD.Attribute value", type text}, {"OSBSTD.Attribute value_7", type text}, {"OSBSTD.Attribute value_8", type text}, {"OSBSTD.Attribute value_9", type text}, {"OSBSTD.Attribute value_10", Int64.Type}, {"OSBSTD.Attribute value_11", Int64.Type}, {"OSBSTD.Attribute value_12", Int64.Type}, {"OSBSTD.Attribute value_13", Int64.Type}, {"OSBSTD.Attribute value_14", Int64.Type}, {"Attribute model", type text}, {"Action text", type text}, {"Text", type text}, {"OSBSTD.Text", type text}, {"Customer information", type text}, {"Cash code", type text}, {"Agreement order type", type text}, {"Assignment class", type text}, {"Assignment category", Int64.Type}, {"Actual start date  a", Int64.Type}, {"Actual finish date", Int64.Type}, {"Service expense cate", Int64.Type}, {"Price list", type text}, {"Individual item type", Int64.Type}, {"Owner", type text}, {"Brand", type text}, {"User defined field 1", Int64.Type}, {"User defined field 2", Int64.Type}, {"User defined field 3", type text}, {"User defined field 4", Int64.Type}, {"User defined field 5", type text}, {"User defined field 6", type text}, {"User defined field 7", Int64.Type}, {"User defined field 8", type text}, {"User defined field 9", type text}, {"Delivered not invoic", Int64.Type}, {"Entry date", Int64.Type}, {"Entry time", Int64.Type}, {"Change date", Int64.Type}, {"Change number", Int64.Type}, {"Changed by", type text}, {"Add. Status (First Fix)", type text}, {"Postal code", Int64.Type}, {"City", type text}, {"Technician name", type text}, {"BC Level 1 name", type text}, {"BC Level 2 name", type text}, {"BC Level 3 name", type text}, {"BC Level 4 name", type text}, {"Charge", type any}, {"Charge name", type any}, {"Charge value", type any}, {"charge account", type any}, {"Planned start date", type text}, {"Planned finish date", Int64.Type}}),
    #"Geänderter Typ1" = Table.TransformColumnTypes(#"Geänderter Typ",{{"Planned start date", type date}, {"Planned finish date", type text}}),
    #"Geänderter Typ2" = Table.TransformColumnTypes(#"Geänderter Typ1",{{"Planned finish date", type date}}),
    #"Hinzugefügte benutzerdefinierte Spalte" = Table.AddColumn(#"Geänderter Typ2", "Month Amount", each Date.Month([Planned finish date])-Date.Month([Planned start date])+1),
    #"Gefilterte Zeilen" = Table.SelectRows(#"Hinzugefügte benutzerdefinierte Spalte", each ([Month Amount] >1)),
    #"Hinzugefügte benutzerdefinierte Spalte1" = Table.AddColumn(#"Gefilterte Zeilen", "Duplicate Line", each List.Repeat({""},[Month Amount])),
    #"Erweiterte Duplicate Line" = Table.ExpandListColumn(#"Hinzugefügte benutzerdefinierte Spalte1", "Duplicate Line"),
    #"Hinzugefügte benutzerdefinierte Spalte2" = Table.AddColumn(#"Erweiterte Duplicate Line", "Line amount local c new", each [Line amount  local c] / [Month Amount]),
    #"Hinzugefügte benutzerdefinierte Spalte3" = Table.AddColumn(#"Hinzugefügte benutzerdefinierte Spalte2", "SalesPeriod", each Date.AddMonths([Planned start date], [Month Amount]-1)),
    #"Geänderter Typ3" = Table.TransformColumnTypes(#"Hinzugefügte benutzerdefinierte Spalte3",{{"Invoice number", type text}}),
    #"Group Invoice number" = Table.Group(Quelle, {"Invoice number"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"data", each _, type table [#"Invoice number"=nullable text, Count up=nullable text]}})
in
    #"Group Invoice number"

Like this you mean?

[Vijay_A_Verma]

Solution file upload to - https://1drv.ms/x/s!Akd5y6ruJhvhuSx85gpq2K8FOskP?e=KNg8N5

Use below code

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Invoice no.", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Invoice no."}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Count up", each {1..[Count]}),
    #"Expanded Count up" = Table.ExpandListColumn(#"Added Custom", "Count up"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Count up",{"Count"})
in
    #"Removed Columns"