Conditiomally Transposing 2 columns

Question

I have 2 columns from a read only MySQL view source.
The table is supposed to have a 4 digit number and possibly a letter in the “Code” Column and a brief text translation associated with the “Code” in the “Translation” Column.
Unfortunately they are swapped between the 2 columns in some rows.
How can I do the following steps:

1. Fix it so all “Code” are in Code Column and Vice Versa
2. Use Text.PadStart to get the leading 0s back so it’s always at least 4 numbers and possibly a letter
3. Sort ascending “Code Column”

Attached is a sample table.43495-sample-translation.pdf

Answer 1

@Jody Fielder

Another option

let  
    Source = Table.FromRows(  
        {{1,"Sample1"},{900,"Sample2"},{"Sample3",1001},{"Sample4",1002},  
        {"2001A","Sample5"},{"2001B","Sample6"},{5000,"Sample7"},{6000,"Sample8"},  
        {"2001B","Sample9"},{"Sample10","3A"}}, {"Code","Translation"}  
    ),  
    ChangeTypes = Table.TransformColumnTypes(Source,  
        {{"Code", type text},{"Translation", type text}}  
    ),  
    ActualCode = Table.AddColumn(ChangeTypes, "Actual.Code", each  
        try Text.From(Number.From(Text.Start([Code],4))) & Text.Middle([Code],4)  
        otherwise [Translation],  
        type text  
    ),  
    ActualTranslation = Table.AddColumn(ActualCode, "Actual.Translation", each  
        if [Code]=[Actual.Code] then [Translation] else [Code],  
        type text  
    ),  
    RemoveColumns = Table.SelectColumns(ActualTranslation,  
        {"Actual.Code", "Actual.Translation"}  
    ),  
    PadCode = Table.TransformColumns(RemoveColumns,  
        {"Actual.Code", each Text.PadStart(_,4,"0"), type text}  
    ),  
    ColumnNames = Table.TransformColumnNames(PadCode, each  
        Text.AfterDelimiter(_,".")  
    ),  
    SortCode = Table.Sort(ColumnNames,{{"Code", Order.Ascending}})  
in  
    SortCode  

Answer 2

Excel 365 Pro Plus with Power Query.
"M" program based on finding a pattern difference
between "Code" and "Translation" data.
http://www.mediafire.com/file/q719wkre2bk6u23/11_29_20.xlsx/file
http://www.mediafire.com/file/1l1mkmfl40imh15/11_29_20.pdf/file

Answer 3

That gives this error: Expression.Error: We cannot convert a value of type Function to type Text. Details: Value=[Function] Type=[Type]

Answer 4

Here is my Query:

let
    Source = // Referenced Query,
    #"ChangeTypes" = Table.TransformColumnTypes(Source,
         {<!-- -->{"Code", type text},{"Translation", type text}}
    ),
    #"ActualCode" = Table.AddColumn(#"ChangeTypes", each
         try Text.From(Number.From(Text.Start([Code],4))) & Text.Middle([Code],4)
         otherwise [Translation],
         type text
     ),
     #"ActualTranslation" = Table.AddColumn(ActualCode, "Actual.Translation", each
         if [Code]=[Actual.Code] then [Translation] else [Code],
         type text
     ),
     #"RemoveColumns" = Table.SelectColumns(ActualTranslation,
         {"Actual.Code", "Actual.Translation"}
     ),
     PadCode = Table.TransformColumns(RemoveColumns,
         {"Actual.Code", each Text.PadStart(_,4,"0"), type text}
     ),
     #"ColumnNames" = Table.TransformColumnNames(PadCode, each
         Text.AfterDelimiter(_,".")
     ),
     #"SortCode" = Table.Sort(ColumnNames,{<!-- -->{"Code", Order.Ascending}})
 in
     SortCode

Answer 5

@Jody Fielder

Not sure what you did but the column name ("Actual.Code") parameter (before each) is missing in:

     #"ActualCode" = Table.AddColumn(#"ChangeTypes", each  
          try Text.From(Number.From(Text.Start([Code],4))) & Text.Middle([Code],4)  
          otherwise [Translation],  
          type text  
      ),  

Fix it according to the code I posted:

     #"ActualCode" = Table.AddColumn#"ChangeTypes", "Actual.Code", each  
         try Text.From(Number.From(Text.Start([Code],4))) & Text.Middle([Code],4)  
         otherwise [Translation],  
         type text  
     ),