Custom Column

[Anonymous]

Hi Experts

Need a custom Column to do the following please.

If Column A begins with G1 in column A and UF71 Column B - Direct,

If Column A does not end in a 1 i.e G2 then Alternative,

If Column A is G2 and column B is UF72 - Direct,,,

If Column A is G3 and column B is UF73 - Direct,,,and so on

if column A iss ZZ and column B is UF71 ignore...

Sample

Column A	Column B	Result
G1	UF71	Direct
G2	UF72	Direct
G3	UF73	Direct
G4	UF74	Direct
G5	UF75	Direct
G6	UF76	Direct
G7	UF77	Direct
G8	UF78	Direct
ZZ	UF71	Ignore
ZZ	UF72	Ignore
ZZ	UF73	Ignore
ZZ	UF74	Ignore
ZZ	UF75	Ignore
ZZ	UF76	Ignore
ZZ	UF77	Ignore
ZZ	UF78	Ignore
G1	UF72	Alternative
G1	UF73	Alternative
G1	UF74	Alternative
G1	UF75	Alternative
G1	UF76	Alternative
G1	UF77	Alternative
G1	UF78	Alternative
G2	UF73	Alternative
G2	UF74	Alternative
G2	UF75	Alternative
G2	UF76	Alternative
G2	UF77	Alternative
G2	UF78	Alternative
G2	UF71	Alternative

[v-stephen-msft · Accepted solution]

Hi @Anonymous ,

Try to add a custom column in PQ

if [Column A]="ZZ" then "Ignore" else if Text.End([Column A],1) = Text.End([Column B],1) then "Direct" else "Alternative"

You can check details from the attachemet.

Best Regards,

Stephen Tao

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

[amitchandak]

@Anonymous , You have to create a new column like

Switch( true(),
left([Column A],2) ="G1" && [Column B] = "UF71", "Direct",
left([Column A],2) ="G2" && [Column B] = "UF72", "Direct",
left([Column A],2) ="G3" && [Column B] = "UF73", "Direct",
right([Column A],1) <> "1" , "Alternative",
left([Column A],2) ="ZZ","Ignore"
)

Add more conditions if needed

[Anonymous]

Hi Amit Apologies.... Can thisbe done in Power Query re Custom Column...Not calculated Column...