Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
BachFel
Helper II
Helper II

Zero infornt of a 4 digit post code

Hi there,

 

is it possible to put a zero infront of the postcodes which are only 4 digits long?

 

Like the rowdata is 4758 but it should be 04758

1 ACCEPTED SOLUTION

In the Query Editor, I'd use Text.PadStart and add leading zeroes up to a length of 5 digits.

Indeed, the values must be of type text.

 

let
    Source = #table(type table[postcode = text],{{"4758"},{"123"}}),
    LeadingZeroes = Table.TransformColumns(Source, {{"postcode", each Text.PadStart(_,5,"0"), type text}})
in
    LeadingZeroes

 

You can find information on Power Query functions in Bemint - Power Query (M) Functions dashboard.

Specializing in Power Query Formula Language (M)

View solution in original post

2 REPLIES 2
jthomson
Solution Sage
Solution Sage

It should be, although I'd guess it'd need to be formatted as text if it isn't already. The approach I'd take is to make a calculated column that looks at the length of the current column (in DAX you'd use the LEN function, but I'd probably use Text.Length in M), and then if it's 4, concatenate a 0 in front otherwise retain the existing value

In the Query Editor, I'd use Text.PadStart and add leading zeroes up to a length of 5 digits.

Indeed, the values must be of type text.

 

let
    Source = #table(type table[postcode = text],{{"4758"},{"123"}}),
    LeadingZeroes = Table.TransformColumns(Source, {{"postcode", each Text.PadStart(_,5,"0"), type text}})
in
    LeadingZeroes

 

You can find information on Power Query functions in Bemint - Power Query (M) Functions dashboard.

Specializing in Power Query Formula Language (M)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.