Nov 24 2020 05:16 AM - edited Nov 24 2020 05:20 AM
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
Nov 24 2020 06:10 AM
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
Nov 25 2020 02:31 AM
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
Nov 25 2020 05:46 AM
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]}
}
),