Add a row to count rows in power query

[Syndicate_Admin]

Hi - i am hoping someone can help.

I am attempting to add a custom column in power query for powerbi desktop. The code i have is below, but i am getting a total count (14271), not a count of the filtered rows as shown in the table below the code - any idea how what i am doing incorrectly?

= Table.AddColumn( #"Expanded List_for_Est_Dates", "EST_ROW_COUNT", (row) => let SourceTable = #"Expanded List_for_Est_Dates", FilteredTable = Table.SelectRows( SourceTable, each [Dates] = row[Dates] and [ProjectID] = row[ProjectID] and [DAPolyID] = row[DAPolyID] and [ProductionUnitID] = row[ProductionUnitID] ), RowCount = Table.RowCount(FilteredTable) in RowCount, type number )

Expected results

Date	ProjectID	DAPOLYID	PRODUCTIONUNITID	ROW COUNT
1/1/2022	10	15	20	2
1/1/2022	10	15	20	2
1/1/2022	12	20	20	2
1/1/2022	12	20	20	2
1/5/2022	10	15	20	3
1/5/2022	10	15	20	3
1/5/2022	10	15	20	3

[wdx223_Daniel · Accepted solution]

= Table.Combine(Table.Group( #"Expanded List_for_Est_Dates",{"Dates","ProjectID","DAPolyID","ProductionUnitID"},{"n",each Table.AddColumn(_, "EST_ROW_COUNT", (x)=>Table.RowCount(_))})[n])

[Anonymous · Accepted solution]

Hi @Syndicate_Admin 

You can put the following code to advanced editor in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJS0lEyNAARpkDCyEApVod4OSOIMJFypnjMJEMuFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, ProjectID = _t, DAPOLYID = _t, PRODUCTIONUNITID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"ProjectID", Int64.Type}, {"DAPOLYID", Int64.Type}, {"PRODUCTIONUNITID", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.RowCount(Table.SelectRows(#"Changed Type",(x)=>x[Date]=[Date] and x[ProjectID]=[ProjectID] and x[DAPOLYID]=[DAPOLYID] and x[PRODUCTIONUNITID]=[PRODUCTIONUNITID])))
in
    #"Added Custom"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[wdx223_Daniel · Accepted solution]

= Table.Combine(Table.Group( #"Expanded List_for_Est_Dates",{"Dates","ProjectID","DAPolyID","ProductionUnitID"},{"n",each Table.AddColumn(_, "EST_ROW_COUNT", (x)=>Table.RowCount(_))})[n])