Count rows if column contains specific text, but column have multiple text in cells

[einfraulein]

Hello dear community,

i hope you're doing well. 

My problem:  i have a table "Countries" with two columns "Alpha-2-code" and " Country".

Table "Countries": 

Alpha-2 code	English short name (upper/lower case)
AD	Andorra
AE	United Arab Emirates
AF	Afghanistan
AG	Antigua and Barbuda
AI	Anguilla
AL	Albania
AM	Armenia
AO	Angola
AQ	Antarctica
AR	Argentina
AS	American Samoa
AT	Austria
AU	Australia
AW	Aruba
AX	Aland Islands !Åland Islands
AZ	Azerbaijan
BA	Bosnia and Herzegovina
BB	Barbados
BD	Bangladesh
BE	Belgium
BF	Burkina Faso
BG	Bulgaria
BH	Bahrain
BI	Burundi
BJ	Benin
BL	Saint Barthelemy !Saint Barthélemy
BM	Bermuda
BN	Brunei Darussalam
BO	Bolivia (Plurinational State of)
BQ	Bonaire, Sint Eustatius and Saba
BR	Brazil
BS	Bahamas
BT	Bhutan
BV	Bouvet Island
BW	Botswana
BY	Belarus
BZ	Belize
CA	Canada
CC	Cocos (Keeling) Islands
CD	Congo (Democratic Republic of the)
CF	Central African Republic
CG	Congo
CH	Switzerland
CI	Cote d'Ivoire !Côte d'Ivoire
CK	Cook Islands
CL	Chile
CM	Cameroon
CN	China
CO	Colombia
CR	Costa Rica
CU	Cuba
CV	Cabo Verde
CW	Curacao !Curaçao
CX	Christmas Island
CY	Cyprus
CZ	Czech Republic

And i have a table " AMT-Query" with a lot of columns, BUT the column "MarketInfo" is the "Problem".

As you can see, there are sometimes several coutry codes in one cell.
I am looking for a way that based on the table "Countries", I can calculate the number (not distinct) of rows if the column "MarketInfo" contains this selected "Alpha-2-code".

What I would like to see:

Alpha-2 code	English short name (upper/lower case)	Total sum if "Alpha-2-code" is found in cell from "MarketInfo"
AD	Andorra	45 (bc found in 45 rows in column "MarketInfo")
AE	United Arab Emirates
AF	Afghanistan
AG	Antigua and Barbuda
AI	Anguilla
AL	Albania
AM	Armenia
AO	Angola
AQ	Antarctica
AR	Argentina
AS	American Samoa
AT	Austria
AU	Australia
AW	Aruba
AX	Aland Islands !Åland Islands
AZ	Azerbaijan
BA	Bosnia and Herzegovina
BB	Barbados
BD	Bangladesh
BE	Belgium
BF	Burkina Faso
BG	Bulgaria

I have tried to split the MarketInfo column into max 10 columns, so that I have the individual Country codes in the 10 columns and then calculate the count when the country is found from "Alpha-2-code" in the columns "MarketInfo 1 - 10".

I tried with lookup, search, contains, but I'm quite new to PBI and need to learn a lot more about the formula structure.

Calculation explanation:

Data from column "MarketInfo"	Calculation for formula
FR	= 1 for "FR"
SA	= 1 for "SA"
AL AL	= this should be count as 1 for Country "AL", bc its doubled
AR	= 1 for "AR"
AR	= 1 for "AR"
AR MX	= 1 for AR, 1 for MX
AR MX	= 1 for AR, 1 for MX
AT
AT
AT AT
AZ GE
BE
BE
BE DE FR
BE DE FR NL
BE FR LU NL
BE FR UK NL
BE LU
BE LU NL
BE NL LU
BG
BG
BG GR RO
BG GR RO RO
BG HR RO
BG HR RO
BG HR RS
BG HR RS
BG HR RS ME
BG HU
BG RO HR
BH IQ JO KW LB OM QA SA AE
BJ BF TD DJ GA GN CM CG MG ML MR NE CF SN TG CI

Thanks a lot and i hope that my explanation is understandable.

Sarah

[Ashish_Mathur]

Hi,

In the Query Editor, split that column by rows (not columns).

[v-yueyunzh-msft · Accepted solution]

Hi , @einfraulein 

According to your description,you want to create a new column in the "Countries" table to count the number of occurrences in AMT-Query[MarketInfo], and only once in a row if it appears consecutively. Right?

Here are the steps you can refer  to :
(1)This is my test data:

(2)We can add a "blank query" in Power Query Editor:

let
    Source = Table.TransformColumns(#"AMT-Query",{"MarketInfo",(x)=>List.Distinct(Text.Split(x," "))     }),
    #"Expanded MarketInfo" = Table.ExpandListColumn(Source, "MarketInfo")
in
    #"Expanded MarketInfo"

(3)Then we can add a custom column in "Countries" Table :

(x)=>  Table.RowCount(Table.SelectRows(Query1,(y)=>y[MarketInfo]=x[#"Alpha-2 code"]  ))

(4)Then we can meet your need:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly