Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
2 years ago
Solved

Add a row to count rows in power query

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

DateProjectIDDAPOLYIDPRODUCTIONUNITIDROW COUNT
1/1/20221015202
1/1/20221015202
1/1/20221220202
1/1/20221220202
1/5/20221015203
1/5/20221015203
1/5/20221015203
     

 

  • wdx223_Daniel's avatar
    wdx223_Daniel
    2 years ago

    = 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's avatar
    Anonymous
    2 years ago

    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.

     

2 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    2 years ago

    = 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's avatar
    Anonymous
    Not applicable
    2 years ago

    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.