question

JodyFielder-1504 avatar image
0 Votes"
JodyFielder-1504 asked ·

Conditiomally Transposing 2 columns

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


power-query-desktoppower-query-mpower-query-connectors
· 2
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@JodyFielder-1504
Threads with tag "office-excel-itpro" focus more on general question on Excel code.
Issues of Power Query formula language are out of our support scope, I would remove the tag "office-excel-itpro".
Thanks for your understanding.

0 Votes 0 ·

my apologies i didn't really know what tag i was supposed to do. lol

0 Votes 0 ·
Lz-3068 avatar image
0 Votes"
Lz-3068 answered ·

@JodyFielder-1504

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
· 1 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @JodyFielder-1504

If this solves your problem could you please mark as answer to help others with a similar scenario? Thanks

1 Vote 1 ·
HerbertSeidenberg-6295 avatar image
0 Votes"
HerbertSeidenberg-6295 answered ·

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

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JodyFielder-1504 avatar image
0 Votes"
JodyFielder-1504 answered ·

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


·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JodyFielder-1504 avatar image
0 Votes"
JodyFielder-1504 answered ·

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



·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Lz-3068 avatar image
0 Votes"
Lz-3068 answered ·

@JodyFielder-1504

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
      ),



·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.