The G29 Z4 M40i sounds amazing with an Akrapovic exhaust. Its expensive but the titanium construction saves weight and the sound is incredible. Worth every penny imo.
I have the Remus sport exhaust on my Z4 M40i and its perfect for daily driving. Deep tone at low RPM, proper growl when you step on it, but no drone at highway speeds. The Akrapovic is louder and sounds amazing but its 3x the price. For a good middle ground, consider a custom catback with a valvetronic muffler - you get the best of both worlds with a button to switch between quiet and loud.
The stock exhaust on the G29 M40i has active valves from factory. Before buying an aftermarket exhaust, try coding the exhaust valves to stay open all the time using Bimmercode. It makes a surprising difference in sound for zero cost. If thats still not enough, then look at aftermarket options.