Forum Discussion

dantyson80's avatar
dantyson80
Frequent Visitor
4 years ago
Solved

Convert column into measure

I have a ZIP code column that needs cleaned up to use for a map visual. Some of the zip codes are Canadian (start with letters) and the rest are USA. Some of the US-based ZIP codes are missing leadin...
  • dantyson80's avatar
    dantyson80
    4 years ago

    I eventually figured out a solution, which is a bit of a workaround unfortunately. In Power Query, I first created a Custom Column that shows me number of characters in the ZIP column:

    Then I was able to create a query that adds the leading zero if the length was less than 5:

     

Jihwan_Kim's avatar
Jihwan_Kim
Super User
4 years ago

Hi,

I am not 100% sure without seeing your pbix file, but please try to check data type and category type of your column.

I suggest searching "power bi column category type zip code" in Edge or Google.

Thanks.

dantyson80's avatar
dantyson80
Frequent Visitor
4 years ago

The Data category is "Postal code" for ZipCode. I don't think I can send the file due to a large amount of sensitive information. Are there additional screenshots that might help?

  • dantyson80's avatar
    dantyson80
    Frequent Visitor
    4 years ago

    Can anyone provide further assistance? I'm still amazed how seemingly difficult it is to get DAX to recognize a column as a measure. Unbelievable.