Help standardizing format from inconsistently formatted data in PQ

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:

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.

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

You can use Fuzzy merge

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

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.