Create a new column from more columns other table

[lboldrino]

i have two tables_ fact_fahrzeuge:

kennzeichen

W12300

W12301

W12302

W12303

W12304

W12305

W12307

dim_sachkonto

counter	name	position	text	bez
1	name1	W12300 in text 1		W12300_555565
2	name2	W12301  in text
3	name3	W12302  in text 11	W12302_34543	W12302 dkfkjregn
4	name4	W12303  in text 2		W12303 terere
5	name5	W12304  in text 3		W12304 fjjgt
6	name6	bzzz	W!2305-text2
7	name7	W12306
8	name8	W12307 ztutz

i need a new column "kennzeichen_col" in dim_sachkonto, if position or text or bez contains kennzeichen from fact_fahrzeuge.

counter	name	position	text	bez	Kennzeichen_col
1	name1	W12300 in text 1		W12300_555565	W12300
2	name2	W12301  in text			W12301
3	name3	W12302  in text 11	W12302_34543	W12302 dkfkjregn	W12302
4	name4	W12303  in text 2		W12303 terere	W12303
5	name5	W12304  in text 3		W12304 fjjgt	W12304
6	name6	bzzz	W12305-text2		W12305
7	name7	W12306
8	name8	W12307 ztutz			W12307

any idea please! 🙂

[Samarth_18 · Accepted solution]

Hi @lboldrino 

You can create a column with below code

Column = 
CALCULATE(FIRSTNONBLANK(fact_fahrzeuge[kennzeichen],1),
FILTER(ALL(fact_fahrzeuge),CONTAINSSTRING(dim_sachkonto[bez],fact_fahrzeuge[kennzeichen]) || 
CONTAINSSTRING(dim_sachkonto[position],fact_fahrzeuge[kennzeichen]) ||
CONTAINSSTRING(dim_sachkonto[text],fact_fahrzeuge[kennzeichen])))

Note:- W12305 it is not coming for me because its copied as  W!2305 in my data table. But it will works for you 🙂

Thanks

[fhill]

What happens if Position = "W12300" and Text contains "W12301", etc?  Do you wan an Error Result if conflicting data in the columns, or do you want the columns to have an Success Order, ignoring the other columns if a result is found?

Forrest

[lboldrino]

What happens if Position = "W12300" and Text contains "W12301" - not possible. 

but for example W12306 is not exist in dim_sackkonto, deshalb null