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

BA_Pete

Super User

4 years ago

Hi Anonymous ,

*EDIT* You answered just at the same time I did! So it's not the first issue.

Can you copy and paste your whole M code into a code window (</> button above) please?

Once I've translated it from Germa, I should be able to add my code into it.

*************************************************************************

My guess is either that your column name has hidden characters e.g. "Invoice number ", or you remove that column in a step prior to trying to group.

Pete

Anonymous

Not applicable

4 years ago

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?

Anonymous

Not applicable

4 years ago

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?

BA_Pete

Super User

4 years ago

Aaaah...I see.

You're trying to refer back to the source in the group step.

Make the end of your query look like this:

    #"Geänderter Typ3" = Table.TransformColumnTypes(#"Hinzugefügte benutzerdefinierte Spalte3",{{"Invoice number", type text}}),
    //The above step hasn't changed, it's just there for reference

    #"Group Invoice number" = Table.Group(#"Geänderter Typ3", {"Invoice number"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"data", each _, type table [#"Invoice number"=nullable text, Count up=nullable text]}})
    addNestedIndex = Table.TransformColumns(#"Group Invoice number", {"data", each Table.AddIndexColumn(_, "Index", 1, 1)}),
    expandIndex = Table.ExpandTableColumn(addNestedIndex, "data", {"Index"}, {"Index"})
in
    expandIndex

Pete

Forum Discussion

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

Related content

Paginated Report headers not repeating

Sort by another column

Sort column with repeated values by another column

Find Most Recent Date Relative to another Date Column in Power Query

Yet another Sort by another Column error.