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
ngiam
Helper I
Helper I

Converting Different Data Format into one Standardized Format

Hi All,

 

How do I convert raw data with the following discrepency into one standardized format?

 

Thank you in advance!

Example;

 

Compiled DataStandardized Format -> AA0000XXXX
AA4829AA00004829
AA00001929AA00001929
(AA00009401)AA00009401
AA125AA00000125
  
1 ACCEPTED SOLUTION
Vijay_A_Verma
Super User
Super User

In a custom column, use following formula

=Text.Select([Compiled Data],{"A".."Z","a".."z"})&Text.PadStart(Text.Select([Compiled Data],{"0".."9"}),8,"0")

View solution in original post

1 REPLY 1
Vijay_A_Verma
Super User
Super User

In a custom column, use following formula

=Text.Select([Compiled Data],{"A".."Z","a".."z"})&Text.PadStart(Text.Select([Compiled Data],{"0".."9"}),8,"0")

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.

Top Solution Authors
Top Kudoed Authors