Conditiomally Transposing 2 columns

Jody Fielder 26 Reputation points
2020-11-29T19:33:57.527+00:00

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

Not Monitored
Not Monitored
Tag not monitored by Microsoft.
37,078 questions
{count} votes

Accepted answer
  1. Lz._ 8,991 Reputation points
    2020-11-30T08:56:13.27+00:00

    @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  
    

4 additional answers

Sort by: Most helpful
  1. Herbert Seidenberg 1,191 Reputation points
    2020-11-30T01:48:55.36+00:00

    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

    0 comments No comments

  2. Jody Fielder 26 Reputation points
    2020-11-30T15:42:29.523+00:00

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

    0 comments No comments

  3. Jody Fielder 26 Reputation points
    2020-11-30T15:45:22.267+00:00

    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
    
    0 comments No comments

  4. Lz._ 8,991 Reputation points
    2020-11-30T21:22:47.197+00:00

    @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  
         ),  
    
    
    
      
    
    0 comments No comments