Trim all Zeros within a text string - Dax DirectQuery

[Haidarius]

Hello, 

I have a text string that has multiple zeros along it. Since I have the data loaded in DirectQuery, I cannot do much in transformation tab and it most probably has to be through DAX, I am thinking of creating an IF conditions that scans each character and basically replaces the zeros into "" blanks..

Example:

016S0200N -> 16S2N

170W0220S -> 17W22S

[mahoneypat · Accepted solution]

Not sure how well it will work with DQ, but you can try to use the SUBSTITUTE function and replace "0" with "".

SUBSTITUTE - DAX Guide

Pat