Calculated measure returning Text

[radovankubis]

HI Guys, I am quite new in the community - please be patient with me.

I have below table:

Mfr Pt No - MD04	Product Description	STRAT	SYD1-01	MAC1-01	SYD1-SN	SYD1-WL
24888	WHEEL C/OFF A46TBF41 M/INOX 127X2.0X22MM	A		200
24888	WHEEL C/OFF A46TBF41 M/INOX 127X2.0X22MM	B	319
24888	WHEEL C/OFF A46TBF41 M/INOX 127X2.0X22MM	H			15
24888	WHEEL C/OFF A46TBF41 M/INOX 127X2.0X22MM	G				10
25205	SCREW SET HEX HD SS316 METRIC 6X30MM	B	361	3062
25205	SCREW SET HEX HD SS316 METRIC 6X30MM	H				0

What am I trying to achieve is to create 4 measures (or columns) which would retrieve the respective STRAT - for product and for MAC, SYD,..

Mfr Pt No - MD04	Product Description		SYD1-01	Strat SYD1-01	MAC1-01	Strat MAC1-01	SYD1-SN	Strat SYD1-SN	SYD1-WL	Strat SYD1-WL
24888	WHEEL C/OFF A46TBF41 M/INOX 127X2.0X22MM	319	B	200	A	15	H	10	G
25205	SCREW SET HEX HD SS316 METRIC 6X30MM		361	B	3062	B			0	H
25925	PAIL INC LID WHITE PLASTIC 20L		1629	A	7731	A			87	B
25823	PAIL INC LID WHITE PLASTIC 10L		0	A	1493	A	9	A	113	A

Could you please help me please.

[v-yuta-msft · Accepted solution]

Hi radovankubis,

To achieve your requiremnet, please refer to steps below:

1. Click Query Editor->Add Column->Custom Column and add 4 custom columns using formula:

    #"Added Custom" = Table.AddColumn(#"Changed Type", "Strat SYD1-01", each if [#"SYD1-01"] <> null then [STRAT] else null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Strat MAC1-01", each if [#"MAC1-01"] <> null then [STRAT] else null),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Strat SYD1-SN", each if [#"SYD1-SN"] <> null then [STRAT] else null),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Strat SYD1-WL", each if [#"SYD1-WL"] <> null then [STRAT] else null)

2. After applied, create a table visual like below:

Regards,

Jimmy Tao

[radovankubis]

Hi All,

I am fairly new the community so please be patient.

I have below table:

Mfr Pt No - MD04	Product Description	STRAT	SYD1-01	MAC1-01	SYD1-SN	SYD1-WL
24888	WHEEL C/OFF A46TBF41 M/INOX 127X2.0X22MM	A		200
24888	WHEEL C/OFF A46TBF41 M/INOX 127X2.0X22MM	B	319
24888	WHEEL C/OFF A46TBF41 M/INOX 127X2.0X22MM	H			15
24888	WHEEL C/OFF A46TBF41 M/INOX 127X2.0X22MM	G				10
25205	SCREW SET HEX HD SS316 METRIC 6X30MM	B	361	3062
25205	SCREW SET HEX HD SS316 METRIC 6X30MM	H				0

what I am trying to achieve is below:

Mfr Pt No - MD04	Product Description		SYD1-01	Strat SYD1-01	MAC1-01	Strat MAC1-01	SYD1-SN	Strat SYD1-SN	SYD1-WL	Strat SYD1-WL
24888	WHEEL C/OFF A46TBF41 M/INOX 127X2.0X22MM	319	B	200	A	15	H	10	G
25205	SCREW SET HEX HD SS316 METRIC 6X30MM		361	B	3062	B			0	H
25925	PAIL INC LID WHITE PLASTIC 20L		1629	A	7731	A			87	B
25823	PAIL INC LID WHITE PLASTIC 10L		0	A	1493	A	9	A	113	A

I am looking for a way how to avieve this STRAT per each Mac/Syd column (its exactly 4 columns)  either through powerquery, calculated column or measure. 

Thank you for your opinions.

Rado