Zip Codes - select

[Peter32]

Hello,

I have a table with the zip code column. This column contains postal codes with 4 digits, but also with more than 4 digits. The latter are then from abroad. Unfortunately I can't get a data field with the mention of the country in the short term.

I only need the 4 digit zip codes so I want to put them in a separate column so I can include this column in all my calculations, reports etc.

They must keep the value "ZIP code" because I want to read them into a heatmap.

Can someone help me with this, thanks in advance.

[parry2k · Accepted solution]

@Peter32 you can add a column in PQ using following code:

if Text.Length([ZipColumn])>4 then null else [ZipColumn]

👉 Learn Power BI and Fabric - subscribe to our YT channel - @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤️

[parry2k]

@Peter32 add a new column using Power Query or DAX, Power Query is preferred. 

Here is DAX:

New Code = LEFT ( YourTable[ZipColumn], 4 )

Here is M code for PQ:

Text.Start([ZipColumn],4)

👉 Learn Power BI and Fabric - subscribe to our YT channel - @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤️

[Peter32]

Thank you for your proposed solution, but unfortunately this doesn't work for me.

I have foreign postal codes with more than 4 digits and domestic postal codes with 4 digits. In your proposal I make all postal codes 4 digits. I just want to exclude postal codes of more than 4 digits so that only the domestic 4-digit postal codes remain.