Unable to recreate an Excel formula in Power Query [closed]

Question

Closed. This question needs debugging details. It is not currently accepting answers.

Edit the question to include desired behavior, a specific problem or error, and the shortest code necessary to reproduce the problem. This will help others answer the question.

Closed last month.

Improve this question

I am trying to recreate this Excel formula in Power Query. I have very little idea what I am doing so I need some help.

=\[@\[DOC ID\]\] &"-"&COUNTIF(\[DOC ID\],\[@\[DOC ID\]\])

I am modifying someone else's Power Query, so any help would be great.

let
    Source = ReqProcBudget2024,
    #"Filtered Rows" = Table.SelectRows(Source, each ([Doc Type] = "PO: ")),
    #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows",null,"",Replacer.ReplaceValue,{"Line\Sched\Dist", "Vendor"}),
    #"Merged Queries" = Table.NestedJoin(#"Replaced Value", {"Budg Dt"}, FY_Dates, {"Date"}, "FY_Dates", JoinKind.LeftOuter),
    #"Expanded FY_Dates" = Table.ExpandTableColumn(#"Merged Queries", "FY_Dates", {"Index"}, {"Index"}),
    #"Removed Duplicates" = Table.Distinct(#"Expanded FY_Dates", {"DOC ID", "Line\Sched\Dist", "Vendor", "Original Amount", "Account", "Department", "Fund"}),
    #"Added Custom" = Table.AddColumn(#"Removed Duplicates", "Show COVID Related POs?", each if Text.Contains([PO Ref], "COVID",Comparer.OrdinalIgnoreCase) then "YES" else "NO"),
    #"Added Conditional Column" = Table.AddColumn(#"Added Custom", "PO Original Amount by Line Item", each if [Original Amount] >= 50000 then "$50k or MORE" else "LESS than $50k"),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "PO Remaining Amount by Line Item", each if [Remaining Amount] >= 50000 then "$50k or MORE" else "LESS than $50k"),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Conditional Column1",{{"Show COVID Related POs?", type text}, {"PO Original Amount by Line Item", type text}, {"PO Remaining Amount by Line Item", type text}})
in
    #"Changed Type"

This is what I have from the Power Query from them. I am just trying to get it to do what I can do in Excel.

I have tried adding:

= Table.AddColumn(#"Changed Type", "UI", each [DOC ID] & "-" & Text.From(Table.RowCount(Table.SelectRows(#"Changed Type", each [DOC ID] = _[DOC ID]))))

It gives me "DOC ID"- 9481 on every line.

It should be "DOC ID"-1 or "DOC ID"- 2 depending on if it is the first instance of the "DOC ID".

Please help.

You can do this with an Index column. For more focused assistance supply a representative data sample and expected results from that sample. — Ron Rosenfeld
– Ron Rosenfeld, Commented Oct 29 at 1:27

Ryan · Accepted Answer · 2025-10-29 01:44:23Z

2

you can try this

create an index column

= Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type)

then create a new column

= Table.AddColumn(#"Added Index", "Custom", each Table.RowCount( Table.SelectRows( #"Added Index",(x)=>x[DOCID]=[DOCID] and x[Index]<=[Index])))

at last ,merge two columns will get that you want

answered Oct 29 at 1:44

Ryan

2,6891 gold badge2 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Unable to recreate an Excel formula in Power Query [closed]

1 Answer 1

Comments

Hot Network Questions