Despite its misleading name, sweet paprika, of which Hungarian paprika is one variety, isn't sugary or sweet at all. Instead, it's called sweet paprika to distinguish it from other types of paprika that are spicy. The end zing, or lack thereof, depends on what type of peppers were used to make the spice. Some paprika is made from red bell peppers, while other paprika is made from chili peppers. If you don't have sweet paprika on hand, you can use any number of substitutes, many of which will yield flavorful results.
Chili powder is a blend that typically consists of ground dried chili peppers, cumin, garlic powder and oregano, among other spices (depending on the recipe or brand). Common in Southwestern cuisine, chili powder offers a complex flavor profile that goes beyond mere heat. It can add depth to dishes, making it a staple in Tex-Mex recipes like chili con carne, tacos and enchiladas.