How to Extract text from word in table 1 and compare with table 2 to create table 3

[Chika100]

[Anonymous]

Hi @Chika100 ,

Did the reply bhanu_gautam offered help you solve the problem, if it helps, you can consider to accept it as a solution so that more user can refer to, or if you have other problems, you can offer some information so that can provide more suggestion for you.

Best regard,

Lucy Chen

[Chika100]

Thank you @bhanu_gautam 

[v-vpabbu]

Hi @Chika100,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,
Vinay Pabbu

[v-vpabbu]

Hi @Chika100,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,
Vinay Pabbu

[GaloyanTelman]

In Power Query (M Language), we can dynamically create new columns for each unique category in Table2 and populate them with 1 or 0 based on whether they exist in Table1[Categories/Text]

let

// Load Table1 and Table2
Source = Table1,
CategoriesList = Table.ToList(Table2), // Get unique categories
// Add columns dynamically, fixing column reference
AddColumns = List.Accumulate(
CategoriesList,
Source,
(tbl, category) =>
Table.AddColumn(
tbl,
category,
each if Text.Contains(Record.Field(_, "Categories/Text"), category) then 1 else 0,
Int64.Type
)
)
in
AddColumns

[bhanu_gautam]

@Chika100 Use the following DAX formula to create Table 3.

Table3 =
VAR Categories = VALUES('Table2'[Categories])
RETURN
ADDCOLUMNS(
'Table1',
"BMW", IF(CONTAINSSTRING('Table1'[Categories/Text], "BMW"), 1, 0),
"Ford", IF(CONTAINSSTRING('Table1'[Categories/Text], "Ford"), 1, 0),
"Ferrari", IF(CONTAINSSTRING('Table1'[Categories/Text], "Ferrari"), 1, 0),
"Mercedes", IF(CONTAINSSTRING('Table1'[Categories/Text], "Mercedes"), 1, 0),
"Tesla", IF(CONTAINSSTRING('Table1'[Categories/Text], "Tesla"), 1, 0),
"Porsche", IF(CONTAINSSTRING('Table1'[Categories/Text], "Porsche"), 1, 0),
"BYD", IF(CONTAINSSTRING('Table1'[Categories/Text], "BYD"), 1, 0),
"MG", IF(CONTAINSSTRING('Table1'[Categories/Text], "MG"), 1, 0),
"Audi", IF(CONTAINSSTRING('Table1'[Categories/Text], "Audi"), 1, 0),
"Skoda", IF(CONTAINSSTRING('Table1'[Categories/Text], "Skoda"), 1, 0),
"Honda", IF(CONTAINSSTRING('Table1'[Categories/Text], "Honda"), 1, 0),
"Kia", IF(CONTAINSSTRING('Table1'[Categories/Text], "Kia"), 1, 0),
"Toyota", IF(CONTAINSSTRING('Table1'[Categories/Text], "Toyota"), 1, 0)
)