Page 1 of 2 1 2 LastLast
Results 1 to 10 of 18

Thread: Power Queries: Can I Get a Count for One Name, Across Three Columns?

  1. #1
    Acolyte jdanniel's Avatar
    Join Date
    Jul 2018
    Posts
    63
    Articles
    0
    Excel Version
    2016

    Power Queries: Can I Get a Count for One Name, Across Three Columns?



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

    I'd like to ask a question about Power Queries, if I may.

    My movie collection spreadsheet has three columns for actors.

    Here's what I'd like to do, if it is possible:

    I'd like to do a Power Query which gives me a total count for each individual actor, regardless of which column their name is in.

    For example, I'd like to know how many movies Paul Newman is in, regardless of whether his name is in the Actor 1 column, the Actor 2 column, or the Actor 3 column.
    Is this possible with Power Queries? Or do I have to use another Excel feature to do this?

    If this can be done, I'd also like to do it for the Genre and Subgenre columns.
    Thank you! Jd
    Attached Files Attached Files

  2. #2
    Conjurer Ron Coderre's Avatar
    Join Date
    Aug 2015
    Location
    Boston, MA
    Posts
    100
    Articles
    0
    Excel Version
    2013, 2016, O365
    I suspect there are more elegant ways to do this...but here's my first pass at it.

    I created a named cell (ActorName) to contain the actor to search for.
    Then I created this query:
    Query Name: CountActorMovies
    M-Code:
    Code:
    let
        ActorName       = Excel.CurrentWorkbook(){[Name="ActorName"]}[Content]{0}[Column1],
        Source          = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
        GetMatches      = Table.AddColumn(
            Table.ReplaceValue(Source,null,"|",Replacer.ReplaceValue,{"Actor 1", "Actor 2", "Actor 3"}), 
            "Custom", 
            each Text.Contains(Text.Upper([Actor 1]&"|"&[Actor 2]&"|"&[Actor 3]),Text.Upper(ActorName))),
        KeepMatchedRows = Table.SelectRows(GetMatches, each ([Custom] = true)),
        AddNameCol      = Table.AddColumn(KeepMatchedRows, "Actor", each ActorName),
        Results         = Table.Group(AddNameCol, {"Actor"}, {{"Movie Count", each Table.RowCount(_), type number}})
    in
        Results
    Does that help?
    Attached Files Attached Files
    Ron Coderre
    Former Microsoft MVP (2006-2015)

  3. #3
    Seeker ExcelStarter's Avatar
    Join Date
    Jan 2018
    Posts
    19
    Articles
    0
    Excel Version
    2016
    Code:
    let
        ActorName=Excel.CurrentWorkbook(){[Name="ActorName"]}[Content]{0}[ActorName],
        Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
        #"Removed Other Columns" = Table.SelectColumns(Source,{"Actor 1", "Actor 2", "Actor 3"}),
        FindText = Table.FindText(#"Removed Other Columns",ActorName),
        #"Calculated Count" = Table.RowCount(FindText)
    in
        #"Calculated Count"
    Attached Files Attached Files

  4. #4
    Conjurer Bill Szysz's Avatar
    Join Date
    Apr 2016
    Location
    Poznań, Poland Dublin, Ireland
    Posts
    103
    Articles
    0
    Excel Version
    Excel 365
    My proposal
    Code:
    let
        ActorName=Excel.CurrentWorkbook(){[Name="ActorName"]}[Content]{0}[Column1],
        Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
        #"Filtered Rows" = Table.SelectRows(Source, each List.Contains({[Actor 1], [Actor 2], [Actor 3]}, ActorName, Comparer.OrdinalIgnoreCase)),
        TheEnd = Table.FromRows({{Text.Proper(ActorName), Table.RowCount(#"Filtered Rows")}}, {"Actor Name", "Movie Count"})
    in
        TheEnd

  5. #5
    Acolyte jdanniel's Avatar
    Join Date
    Jul 2018
    Posts
    63
    Articles
    0
    Excel Version
    2016
    Thank you all for replying, and for contributing code. Being a novice, I'm not entirely sure I explained what I wanted to do very well. Either that, or I don't quite fully understand the code. Probably both.

    Do the code samples above allow me to have one master actors list, with a count for every actor? That's really what I'm looking for. I do not know if the code samples do this. The code samples, as I see them, permit one actor. Am I correct or incorrect about this?

    If you look at my spreadsheet, you'll see a worksheet in it named Director Count. It has a master list of every director in the spreadsheet, along with a count for each director. Granted, the list of directors is in one column, not three. But that's what I was hoping for, with the actors. I'd like one column, with a list of all of the actors in the spreadsheet's three Actors columns. Next to each actors' name should be the count.

    I apologize if I do not understand the code well enough to know if it does this or not. Is there a way to combine the three columns into one, for the specific purpose of having one master Actors list and count? I hope I explained myself better this time, and if I am not getting the gist of this code, I apologize. Thank you.

  6. #6
    Acolyte Nick Burns's Avatar
    Join Date
    May 2017
    Posts
    75
    Articles
    0
    Excel Version
    Office 365
    Something like this:
    Code:
    let    
        Source          = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
        #"Removed Other Columns" = Table.SelectColumns(Source,{"Actor 1", "Actor 2", "Actor 3"}),
        #"Replaced Value" = Table.ReplaceValue(#"Removed Other Columns",null,"|",Replacer.ReplaceValue,{"Actor 1", "Actor 2", "Actor 3"}),
        #"Merged Columns" = Table.CombineColumns(#"Replaced Value",{"Actor 1", "Actor 2", "Actor 3"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged"),
        #"Filtered Rows" = Table.SelectRows(#"Merged Columns", each [Merged] <> "|||||"),
        #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Filtered Rows", {{"Merged", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Merged"),
        #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Merged", type text}}),
        #"Filtered Rows1" = Table.SelectRows(#"Changed Type", each ([Merged] <> "")),
        AllActors = Table.Sort(#"Filtered Rows1",{{"Merged", Order.Ascending}}),
        DistinctActors = Table.Distinct(AllActors),
        #"Merged Queries" = Table.NestedJoin(DistinctActors,{"Merged"},AllActors,{"Merged"},"DistinctActors",JoinKind.LeftOuter),
        #"Aggregated DistinctActors" = Table.AggregateTableColumn(#"Merged Queries", "DistinctActors", {{"Merged", List.Count, "Count of Merged"}}),
        #"Sorted Rows" = Table.Sort(#"Aggregated DistinctActors",{{"Merged", Order.Ascending}})
    in
        #"Sorted Rows"
    This one counts all the "Blank Actors"
    Code:
    let    
        Source          = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
        #"Removed Other Columns" = Table.SelectColumns(Source,{"Actor 1", "Actor 2", "Actor 3"}),
        #"Replaced Value" = Table.ReplaceValue(#"Removed Other Columns",null,"|",Replacer.ReplaceValue,{"Actor 1", "Actor 2", "Actor 3"}),
        #"Merged Columns" = Table.CombineColumns(#"Replaced Value",{"Actor 1", "Actor 2", "Actor 3"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged"),
        #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Merged Columns", {{"Merged", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Merged"),
        #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Merged", type text}}),
        AllActors = Table.Sort(#"Changed Type",{{"Merged", Order.Ascending}}),
        DistinctActors = Table.Distinct(AllActors),
        #"Merged Queries" = Table.NestedJoin(DistinctActors,{"Merged"},AllActors,{"Merged"},"DistinctActors",JoinKind.LeftOuter),
        #"Aggregated DistinctActors" = Table.AggregateTableColumn(#"Merged Queries", "DistinctActors", {{"Merged", List.Count, "Count of Merged"}}),
        #"Sorted Rows" = Table.Sort(#"Aggregated DistinctActors",{{"Merged", Order.Ascending}})
    in
        #"Sorted Rows"
    Last edited by Nick Burns; 2019-02-26 at 11:00 PM. Reason: Added Count for No Actors
    Oh... by the way, YOU'RE WELCOME!

  7. #7
    Acolyte jdanniel's Avatar
    Join Date
    Jul 2018
    Posts
    63
    Articles
    0
    Excel Version
    2016
    YES! That is exactly what I'm looking for. Thank you so very much. Now, to try and learn some M language! See you in a few years! (kidding) Seriously, thank you. Because of this thread, I discovered what M language is. Been reading a little bit about it this afternoon. Thank you again!

  8. #8
    Conjurer Bill Szysz's Avatar
    Join Date
    Apr 2016
    Location
    Poznań, Poland Dublin, Ireland
    Posts
    103
    Articles
    0
    Excel Version
    Excel 365
    Alternatively
    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
        OneColumnTbl = Table.AddColumn(Source, "Actor", each {[Actor 1],[Actor 2],[Actor 3]})[[Actor]],
        #"Expanded {0}" = Table.ExpandListColumn(OneColumnTbl, "Actor"),
        #"Grouped Rows" = Table.Group(#"Expanded {0}", {"Actor"}, {{"Movie Count", each Table.RowCount(_), type number}}),
        #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Actor", Order.Ascending}})
    in
        #"Sorted Rows"

  9. #9
    Acolyte Nick Burns's Avatar
    Join Date
    May 2017
    Posts
    75
    Articles
    0
    Excel Version
    Office 365
    Great!

    One last alteration --
    This one counts where the are NO actors listed:

    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
        #"Removed Other Columns" = Table.SelectColumns(Source,{"Actor 1", "Actor 2", "Actor 3"}),
        #"Replaced Value" = Table.ReplaceValue(#"Removed Other Columns",null,"|",Replacer.ReplaceValue,{"Actor 1", "Actor 2", "Actor 3"}),
        #"Merged Columns" = Table.CombineColumns(#"Replaced Value",{"Actor 1", "Actor 2", "Actor 3"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged"),
        #"Replaced Value1" = Table.ReplaceValue(#"Merged Columns","|||||","<none>",Replacer.ReplaceText,{"Merged"}),
        #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Replaced Value1", {{"Merged", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Merged"),
        #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Merged", type text}}),
        #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Merged] <> "")),
        AllActors = Table.Sort(#"Filtered Rows",{{"Merged", Order.Ascending}}),
        DistinctActors = Table.Distinct(AllActors),
        #"Merged Queries" = Table.NestedJoin(DistinctActors,{"Merged"},AllActors,{"Merged"},"DistinctActors",JoinKind.LeftOuter),
        #"Aggregated DistinctActors" = Table.AggregateTableColumn(#"Merged Queries", "DistinctActors", {{"Merged", List.Count, "Count of Merged"}}),
        #"Sorted Rows" = Table.Sort(#"Aggregated DistinctActors",{{"Merged", Order.Ascending}})
    in
        #"Sorted Rows"
    I wanted to add that this can all be done with the UI, however the #"Merged Queries" step needs to be modified to "Self Join" the AllActors and DistinctActors tables.
    Oh... by the way, YOU'RE WELCOME!

  10. #10
    Acolyte Nick Burns's Avatar
    Join Date
    May 2017
    Posts
    75
    Articles
    0
    Excel Version
    Office 365
    {face palm}
    Had I done the GroupBy, my solution is purely UI driven!

    Though I'm not sure why Bill's solution show 466 blanks, and mine shows 932 (twice as many)
    Oh... by the way, YOU'RE WELCOME!

Page 1 of 2 1 2 LastLast

Posting Permissions

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