topic Re: How to Extract text from word in table 1 and compare with table 2 to create table 3 in DAX Commands and Tips

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

Chika100 — Thu, 13 Mar 2025 09:45:52 GMT

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

bhanu_gautam — Thu, 13 Mar 2025 10:44:05 GMT

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

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

GaloyanTelman — Thu, 13 Mar 2025 11:09:06 GMT

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

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

Chika100 — Thu, 13 Mar 2025 11:50:54 GMT

Thank you bhanu_gautam

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

Anonymous — Tue, 25 Mar 2025 02:13:20 GMT

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

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

Anonymous — Tue, 22 Apr 2025 11:46:39 GMT

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

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

Anonymous — Wed, 30 Apr 2025 08:09:50 GMT

Hi @Chika100,

May I ask if you have gotten this issue resolved?

Regards,
Vinay Pabbu