Forum Discussion
Calculated measure returning Text
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.
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
2 Replies
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
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