How to count the specific text from another table?

[LandOfSmile178]

Hello all,

Thank you for your time of reading my post. Currently, I'm building a measure to count the specific text from another table like in Excel when using formular COUNTIF(range,"*"&row&"*") in the example below but I don't know how to write measure in power BI so I hope you can help me for the solution. Thank you so much.

Scenario: In the excel file below, I have one sheet "product" and one sheet "report", you can see that I use the count formular with specific text to count because when extracting report from server, it will include the suffix after the product name (.....).

product sheetreport sheet

[LandOfSmile178]

Hi @bhelou , thank you for your reply. I tried in power query but the split is not what I want. What I want is just split the Product A, B, C and D into each row:

I can't find the attached file button so below is my table data:

Product

Product A(from USA)

Product C(from UAE)

Product A(from USA) Product B(from Japan)

Product C(from UAE) Product D(from Germany)

Product A(from USA) Product B(from Japan) Product D(from Germany)

Product A(from USA) Product B(from Japan) Product C(from UAE)

Product A(from USA)

Product C(from UAE)

Product A(from USA)

Product C(from UAE)

Product D(from Germany)

Product D(from Germany)

[bhelou]

Dear Attached Image for the final resulet  also attached PBIX file see the steps how to achieve your target .

 
https://drive.google.com/file/d/1OoAFeSam3SNQ6cEUW4AWmmJC7DQo5AgA/view?usp=sharing 


Kindly accept as a solution and a kudo would be greatfull . 

Thanks , 

[LandOfSmile178]

Thank you @bhelou , I will try later on my personal laptop since google drive is forbidden with our policy. Just quick question: do I have to re-do again all these steps in power query if the data is updated with many records in future?

[bhelou]

Hello , 

Its will work  automatik everytime there is a refresh from the system . 

for any inquiries kindly contact me through my email 
bassem.7elou@gmail.com

Thanks , 

[LandOfSmile178]

thank you @bhelou , I have sent you an email for inquiries.

[bhelou · Accepted solution]

Hello , 

1: make a relationship between the 2 tables :  table 1 should be unique as ( product should not be duplicated ) , table 2 it should have Product column ( this can me duplicated as much as you want )  , the relationship shoul go 1 to many 

2 : in report view just make a table and drop the product from the first table in the columns ( dont sumarize it ) , and product from the table 2 and set it as count 

this is an aproach to make a table 
second approach : 

in table 1 that is unique make a new calculated column as per the image attached : 
count = countrows(relatedtable(table 2 )) 

Please accept as a solution if it works with you and a kudo is appreciated . 
* share some sample file if you didnt figure it out 


Thanks , 

[LandOfSmile178]

Hello @bhelou , thank you for your reply. I got your point now but our report system when extracting data always shown 2 or maybe 5 products in one row as image below. 

report sheet

So is there any function that we can split them to one row? Like this

row1: product A product B product C

to 

row1: product A

row2: product B

row3: product C

Thank you.

[bhelou]

Dear , 

Attached power Queries images how to clean data first

[bhelou]

Hello  @LandOfSmile178  ,

yes there is a way :

try to clean the data first from power queries ( split the data by space or delimiter ) , 

kindly share some sample data so we can achieve it , copy paste the data not an image here .