Create a new table from 2 separate table and duplicated lines

[Dacta]

Hello everyone,

I need help with a dax formula on power bi.
I have two tables, one with item names (table1 on the exemple), another table with the same item names but with also descriptions in the same boxes (table2).
I'm trying to create another table allowing me to have a column with the names of the items and another with each word of the description (table_final).
I need exactly one line per description word (on the exemple, Items5 have 2 description word and Items1 3).
The second table can also have the same ITEMS several times (on the exemple, Items2 is here twice).

Picture : (table1)

Picture : (table2)

Picture : (Final table)

Thank you in advance for your help.

[mark_endicott · Accepted solution]

@Dacta - This is best done in Power Query. I would take the following steps:

1) Merge table 1 into table 2 and expand the Items column. 

2) Use "Split by delimiter" on the words column of table 2. Set to split by every occurance of the space delimiter

3) select your new columns in this table (the ones created by the last 2 steps) and then "Unpivot Other Columns". 

4) remove any unecessary columns (which is likely to be the "attribute" column)

I'm fairly sure this will give you the output you are looking for, 

If this helps, please accept as the solution. it helps with visibility for others with the same challenge. 

[Dacta · Accepted solution]

Thanks you everyone for your help!
However, I really need to achieve this function on DAX. This is a request from my company.
For the moment I managed to get a result with this in DAX:

newtable =

    VAR split           =   GENERATE    (   table

                                        ,   ADDCOLUMNS  (   GENERATESERIES  (   1

                                                                            ,   PATHLENGTH  (   [Witout Space]

                                                                                            )

                                                                            )

                                                        ,   "Items Alone"

                                                        ,   PATHITEM    (   [Witout Space]

                                                                        ,   [Value]

                                                                        )

                                                        )

                                        )

    VAR join            =   CROSSJOIN   (   split

                                        ,   table2

                                        )

    VAR filter          =   FILTER  (   join

                                    ,   IF  (   OR  (   [Items Alone]   =   BLANK()

                                                    ,   OR  (   [Items Alone]   =   "H"

                                                            ,   [Items Alone]   =   "F"

                                                            )

                                                    )

                                            ,   BLANK()

                                            ,   [Items Alone]   = table2[Items]

                                                ||  CONTAINSSTRING  (   table2[Items]

                                                                    ,   [Items Alone]

                                                                    )

                                            )

                                    )

RETURN filter

If you have another solution I'm interested !

[mark_endicott]

@Dacta - If your solution works, great. But you should be aware that your company has requested you do something in the least efficient way. The DAX approach will be worse for your data model, because the compression of a calculated table is not as great as the compression of a table in Power Query. This will result in a larger data model and a longer refresh of the data. 

If you wish to do things with best practice, you should tell your company that it's better to do it the way I and @v-zhengdxu-msft have suggested. 

[v-zhengdxu-msft]

Hi @Dacta 

Please try this:

First of all, splite the table2 by delimiter in the power query:

Then in the Table1, click merge queries:

Expand the table:

Select these expanded columns and click unpivot columns

Remove the Attribute column:

The result is as follow:

Best Regards

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

[mark_endicott]

@v-zhengdxu-msft - Thank you for repeating the steps in my solution but with screenshots. 

[mark_endicott · Accepted solution]

@Dacta - This is best done in Power Query. I would take the following steps:

1) Merge table 1 into table 2 and expand the Items column. 

2) Use "Split by delimiter" on the words column of table 2. Set to split by every occurance of the space delimiter

3) select your new columns in this table (the ones created by the last 2 steps) and then "Unpivot Other Columns". 

4) remove any unecessary columns (which is likely to be the "attribute" column)

I'm fairly sure this will give you the output you are looking for, 

If this helps, please accept as the solution. it helps with visibility for others with the same challenge.