Help standardizing format from inconsistently formatted data in PQ

[memote1]

Hello,

I have two datasets I am merging using an ID number. The ID number comes from an external source, and we manually enter it into our system. Unfortunately, it is rarely entered in the correct format, making it impossible to match records. I manually go into our system to fix them, but I am hoping for a solution that doesn't require me to. The proper format is "023459-000000-MS-01". The first 6 digits never change, and always have the leading zero. The next 6 digits are unique, and the "-MS-01" doesn't change.

Below is an example of how the ID number has been entered, with the corresponding correctly formatted ID number.

Thank you!

Raw ID Number	Properly Formatted ID Number
023459000208	023459-000208-MS-01
023459-000235-MS-01	023459-000235-MS-01
23459-00125-MS-01	023459-00125-MS-01
23459000447	023459-000447-MS-01
23459000302-MS-01	023459-000302-MS-01
023459--000375-MS-01	023459-000375-MS-01
023459000170-MS-01	023459-000170-MS-01
023459-000288-ms-01	023459-000288-MS-01
023459-00260	023459-000260-MS-01
23459-00120-ms-01	023459-000120-MS-01

[ronrsnfld · Accepted solution]

You can use this formula in the Add Custom Column dialog:

= [a=Text.Upper([Raw ID Number]),
 b=Text.PositionOf(a,"23459"),
 c=Text.RemoveRange(a,0,7-b),
 d=Text.Replace(c,"MS-01",""),
 e=Text.Trim(d,"-"),
 f=Number.ToText(
     Number.From(e),"""023459-""000000""-MS-01""")][f]

[v-ssriganesh]

Hi @memote1,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[dufoq3]

Hi @memote1, check this:

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjAyNjG1NDAwMDKwUIrVgQnogkSMTXV9g3UNDMHiMGFDIwxRoFoTE3MUvrGBEZIqqJkgQ43NTTEkgMKG5gaY6kFusLDQzS3GEDcyM0BzlAFMWSwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Raw ID Number" = _t]),
    Ad_CorrectID = Table.AddColumn(Source, "Correct ID", each 
        [ col = [Raw ID Number],
          a = Text.PositionOf(col, "9") +1,
          b = Text.Start(col, a),
          start = Text.PadStart(b, 6, "0"),
          mid = Text.PadStart(Text.BeforeDelimiter(Text.TrimStart(Text.RemoveRange(col, 0, Text.Length(b)), {"-"}), "-"), 6, "0"),
          result = Text.Combine({start, mid, "MS-01"}, "-")
        ][result], type text)
in
    Ad_CorrectID

[Omid_Motamedise]

You can use Fuzzy merge

[Akash_Varuna]

Hi @memote1 , Add a custom column to generate the corrected format
Text.PadStart(Text.Middle(Text.Remove(Text.Lower([Raw ID Number]), "-ms-01"), 6), 6, "0") & "-" &
Text.PadStart(Text.Middle(Text.Remove(Text.Lower([Raw ID Number]), "-ms-01"), 6, Text.Length(Text.Remove(Text.Lower([Raw ID Number]), "-ms-01")) - 6), 6, "0") &
"-MS-01"
If this post helped please do give a kudos and accept this as a solution
Thanks In Advance

[v-ssriganesh]

Hi @memote1,
Thanks for posting your query in Microsoft fabric community forum.

I have reproduced your scenario and successfully transformed the data using Power Query in Power BI Desktop. Below are the detailed steps I followed, along with the expected output. I am also attaching a screenshot and PBIX file for your reference.

Step 1: I used the following sample data to simulate your scenario:

Raw ID Number
023459000208
023459-000235-MS-01
23459-00125-MS-01
23459000447
23459000302-MS-01
023459--000375-MS-01
023459000170-MS-01
023459-000288-ms-01
023459-00260
23459-00120-ms-01

Step 2: Load Data into Power Query

1. Open Power BI Desktop.
2. Click on Home → Enter Data (or load from Excel/CSV).
3. Enter the Raw ID Numbers into a table.
4. Click OK, then click Transform Data to open Power Query Editor.

Step 3: Convert All IDs to Uppercase

• Click on the Raw ID Number column.
• Go to Transform → Format → UPPERCASE.
  
  

Step 4: Extract Only Numeric Digits

• Click on Add Column → Custom Column.
• Use the following formula to extract only numbers:

CODE:

CopyEdit

Text.Select([Raw ID Number], {"0".."9"})

• Rename this column to NumericOnly.
  
  

Step 5: Extract the Unique 6-Digit Part

• Click Add Column → Custom Column.
• Use the formula below:
  CODE:

CopyEdit

Text.Middle([NumericOnly], 6)

• Rename it as UniquePart.
  
  

Step 6: Ensure 6 Digits with Leading Zeros

• Click Add Column → Custom Column.
• Use this formula:

CODE:

CopyEdit

Text.PadStart([UniquePart], 6, "0")

• Rename it as PaddedUniquePart.
  
  

Step 7: Construct the Final ID Format

• Click Add Column → Custom Column.
• Use this formula:

CODE:

CopyEdit

"023459-" & [PaddedUniquePart] & "-MS-01"

• Rename it as Formatted ID Number.

Step 8: Finalizing the Data

• Keep only Raw ID Number and Formatted ID Number.
• Click Close & Apply.

Output:

• For your reference, please check the attached .pbix file
  
  

If this helps, then please Accept it as a solution and dropping a "Kudos" so other members can find it more easily.
Thank you.