Query formula to build custom hyperlink.

Hi folks!

I'm trying to build a link with other values from other column that are in the query itself.

Basically I have this as a formula when I put it myself in the workbook table:

="https://mywebpage.com/"&SUBSTITUTE([@Name];" ";"_")&IF(RIGHT([@Name];8)="(scroll)";"";"_")&IF(RIGHT([@Name];8)="Sicarius";"("&[@[Job Type]]&")";"")&IF(SUM(COUNTIF(D:D;[@Name]))-SUM(COUNTIFS(D:D;[@Name];C:C;[@Element]))>0;"("&[@Element]&")";"")&IF(RIGHT([@Name];8)="(scroll)";"";"(Card)")&"#"&LEFT([@Rarity];1)

As of now I made it this far:

="https://mywebpage.com/" & Text.Replace ([Name]," ","_")

It's the IF part that I really have trouble with in the query editor.

Any Query genius out here?