Results 1 to 6 of 6

Thread: Row Count of Merged Table

  1. #1
    Conjurer Nick Burns's Avatar
    Join Date
    May 2017
    Posts
    107
    Articles
    0
    Excel Version
    Office 365

    Row Count of Merged Table



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have a self-join table where I'm trying to find the number of Child Jobs to a Parent Job. I guess you can call this a "COUNTIF()".

    I've done two approaches in my attached file:

    1) Merge the table, expand, Group By all the original columns to get the count of rows
    Pros: Fast - as in mere seconds
    Cons: Although my sample only shows 5 columns, my actual data has about 25 columns and may expand in the future making modifications to the Group By step necessary

    2) Merge the table, add a column that does a Table.RowCount() on the column and removes the merge data
    Pros: alleviates the con in scenario 1
    Cons: Slow - with my data this takes about 8 - 9 minutes.

    Note: my datasource is a CSV file from our ERP system. The attached has sample data as a Table source.

    Am I missing something obvious for this or is there a better approach to this?

    Thanks,
    Nick
    Attached Files Attached Files
    Oh... by the way, YOU'RE WELCOME!

  2. #2
    Conjurer Bill Szysz's Avatar
    Join Date
    Apr 2016
    Location
    Poznań, Poland Dublin, Ireland
    Posts
    122
    Articles
    0
    Excel Version
    Excel 365
    Hi Nick,
    I'm not sure if ithis is exactly what you want.
    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Grouped Rows" = Table.Group(Source, {"Parent"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
        #"Merged Queries" = Table.NestedJoin(Source, {"Parent"}, #"Grouped Rows", {"Parent"},  "Table1", JoinKind.LeftOuter),
        #"Expanded {0}" = Table.ExpandTableColumn(#"Merged Queries", "Table1", {"Count"}, {"Count"})
    in
        #"Expanded {0}"

  3. #3
    Conjurer Nick Burns's Avatar
    Join Date
    May 2017
    Posts
    107
    Articles
    0
    Excel Version
    Office 365
    Quote Originally Posted by Bill Szysz View Post
    Hi Nick,
    I'm not sure if ithis is exactly what you want.
    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Grouped Rows" = Table.Group(Source, {"Parent"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
        #"Merged Queries" = Table.NestedJoin(Source, {"Parent"}, #"Grouped Rows", {"Parent"},  "Table1", JoinKind.LeftOuter),
        #"Expanded {0}" = Table.ExpandTableColumn(#"Merged Queries", "Table1", {"Count"}, {"Count"})
    in
        #"Expanded {0}"
    I love how there are so many solutions to a problem. As this example shows, there are some more proficient than others that are just "clever". And just because something could be done in one line doesn't mean it's the best.

    Your solution is slick, but ooh, I have to ding you some points ... it adds 4 seconds using my real-world data ... I'm kidding! I can live with 4 seconds

    Thanks - this does get around the GroupBy issue. I did come up with another solution on my GroupBy method: Using a step to gather the columns (Table.ColumnNames) then passing this list to my GroupBy step.

    Thank you Bill!
    Oh... by the way, YOU'RE WELCOME!

  4. #4
    Conjurer pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    121
    Articles
    0
    Excel Version
    Office 365
    At the moment I don't understand why the number of columns is relevant for the grouping, because the columns by which the grouping is done won't change. But maybe it will become clearer if you show the coder of your new solution.

  5. #5
    Conjurer Nick Burns's Avatar
    Join Date
    May 2017
    Posts
    107
    Articles
    0
    Excel Version
    Office 365
    Quote Originally Posted by pinarello View Post
    At the moment I don't understand why the number of columns is relevant for the grouping, because the columns by which the grouping is done won't change. But maybe it will become clearer if you show the coder of your new solution.
    The data is coming from our ERP system - the criteria for analysis could change, either by dropping or adding new data (columns). The GroupBy step would break or miss columns if they are hard coded. This was the solution I came up with to alleviate that:

    Code:
    let    
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        ColumnList = Table.ColumnNames(Source),
        #"Merged Queries" = Table.NestedJoin(Source, {"Child"}, Source, {"Parent"}, "Source", JoinKind.LeftOuter),
        #"Expanded Source" = Table.ExpandTableColumn(#"Merged Queries", "Source", {"Parent"}, {"Source.Parent"}),
        #"Grouped Rows" = Table.Group(#"Expanded Source", ColumnList, {{"Count", each Table.RowCount(_), Int64.Type}})
    in
        #"Grouped Rows"
    Contrast to the original:
    Code:
    let    
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Merged Queries" = Table.NestedJoin(Source, {"Child"}, Source, {"Parent"}, "Source", JoinKind.LeftOuter),
        #"Expanded Source" = Table.ExpandTableColumn(#"Merged Queries", "Source", {"Parent"}, {"Source.Parent"}),
        #"Grouped Rows" = Table.Group(#"Expanded Source", {"Child", "EMAC", "BILLED", "UNBILLRET", "Parent"}, {{"Count", each Table.RowCount(_), Int64.Type}})
    in
        #"Grouped Rows"
    Note: I did go with Bill's solution in the end.
    Oh... by the way, YOU'RE WELCOME!

  6. #6
    Conjurer pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    121
    Articles
    0
    Excel Version
    Office 365
    Hi Nick,

    sometimes it takes a little longer, but now after testing the last code myself, I now understand the point of your question and thus the clever solution.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •