Power Query Count if formula

[skunki]

i !

Need support with a count if -formula in PowerQuery. I know how to do it in excel but not in Powerquery.

I need to count the different combinations for name&stage:

the list is a combination of name&city&stage, example:

AdamMunichFab

AdamMadridFab

BetaMunichTest

BetaMadridFab

BetaUlmTest

BetaMunichTest

BestMadridTest

the result should be:

AdamMunichFab        2   (two different cities for Adam and Fab)

AdamMadridFab        2

BetaMunichTest          1

BetaMadridFab            1

BetaUlmTest                 3   (three different cities for Beta and Test)

BetaMunichTest            3

BetaMunichTest          3

BestMadridTest            3

Is this understandable?

thanks for your support!!

skunki

[#2]

@skunki 

First we need splits texts on Name, City and Stage, do that on duplicated column. After that group City and Stage without aggregation, count number of rows for each generated table and expand it keeping finally only NameCityStage and Count.

Generated script is

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Duplicated Column" = Table.DuplicateColumn(Source, "W", "WW"),
    #"Split Column by Character Transition" = Table.SplitColumn(
        #"Duplicated Column",
        "W",
        Splitter.SplitTextByCharacterTransition({"a".."z"}, {"A".."Z"}),
        {"Name", "City", "Stage"}
    ),
    #"Grouped Rows" = Table.Group(
        #"Split Column by Character Transition",
        {"Name", "Stage"},
        {
            {"NameStage",
             each _,
             type table [Name=nullable text, City=nullable text, Stage=nullable text, WW=text]}
        }
    ),
    #"Count number of rows in each table" = Table.AddColumn(
        #"Grouped Rows",
        "Count",
        each Table.RowCount([NameStage])
    ),
    #"Expanded NameStage" = Table.ExpandTableColumn(
        #"Count number of rows in each table",
        "NameStage", {"WW"}, {"WW"}
    ),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded NameStage",{"WW", "Count"})
in
    #"Removed Other Columns"

which gives

[#3]

@Sergei Baklan 

Thanks so much for you support and quick answer.

I'll try that out, unless it sounds complicated to me , I am a beginner in power query.

Can I ask one additional question? Would this also work, if one line would be duplicated? like in my example, if there are two lines with same content ( line: BetaMunichTest) would it count as one entry (desired) or two?

thanks again,

skunki

[#4]

@skunki 

If you mean like this

when by modifying Grouped Rows step as

    #"Grouped Rows" = Table.Group(
        #"Split Column by Character Transition",
        {"Name", "Stage"},
        {
            {"NameStage",
             each Table.Distinct(_, {"WW"}),  // INSTEAD OF each _,
             type table [Name=nullable text, City=nullable text, Stage=nullable text, WW=text]}
        }
    ),