Showing the zero with two digit postcode

[MZCLN]

Hi, 

I created a map from germany with only the two first digit of the postcodes and did the following steps

• used Power BI desktop
• enabled shape map
• created a zip 2 digit TopoJSON file and loaded it into Power BI 
• imported the data: zipcode as text and categorized it as postcode

There are 9 regions in germany that have a postcode starting with 0, so 01, 02, 03... PowerBi seems to not register the postcodes as the one starting with 0 are shown as 1, 2, 3... (grey area) Every other postcode is working. 

Can someone help me to figure out, why powerbi is deleting the 0 infront of the 2 digit postcode, even though they are transfered as postcodes? In the Excel are the 0 shown. 

 

 

I know that there are no values in the column, thats because the data is very big and it takes a while to load. As you can see in the dropdown menues, there are values in the list.

Thank you very much for your help!

 

[Mikelytics · Accepted solution]

Hi MZCLN,

Can you please try the following in Power Query:

1. Make sure that the Zip Code COlumn is text

2. Add a column with the following formula:

if Text.Length([ZIP COdes]) = 1 then "0" & [ZIP COdes] else [ZIP COdes]

3. Result: 

Please now use the new column for your visual.

Best regards

Michael

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your kudos.

[MZCLN]

It worked! Thank you very much, I´ve been on this for a while now! Thanks!

[Mikelytics]

Awesome! Thank you for your feedback! 😊

[v-yalanwu-msft]

Hi, @MZCLN ;

Can you tell me the type about your excel.

In my excel, i change the type to text.

Or you could add "'" before the number.

If not ok, can you share the excel.How to upload PBI in Community
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MZCLN]

Hi yes, I have a custom format with 0#, so it´ll show the zero. If I try it with the text format it´s still not showing the two digit in PowerBI

[v-yalanwu-msft]

Hi, @MZCLN ;

I tested it, and in excel show:

Then in desktop show:

So we could tranform data and in power query . delete last step-change type

the final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MZCLN]

Hi, thanks for your help. Could you show me which format the excel was? Because if I delete the last change type, it won´t change to your format in the power query

 

[Anonymous]

I am guessing it's transforming the column in Power Query when you loaded into PowerBI. Check your applied steps in Power Query and ensure that a transform step wasn't inadvertently added. 

[MZCLN]

In the Power Query are no additional steps, it shows only source, navigation, header put higher and a name change I did. Before I change the format of the 2 digit postcodes to a text format, it is already not showing the 0