I think it’s pretty niche but is a great tool for parsing / converting data into a format that is more easily digested by another program.
Think for example a report from an 80’s system that spits out many tab separated values in a different format based on some code. Then these tables are all separated by two blank lines and order of them is randomised. To top that off you need to then pipe it all to a different program that only accepts a specific format.
You could do it in Python by doing a parse, process, stringify code but if you know awk you can do all those steps at the same time with less code.
Sure, in the age of REST the Python approach is better but awk is a very powerful tool for the “I have a specific output but need a specific input” problem.