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

[Chika100]

[v-xinc-msft]

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 

[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)
)