Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Power Query Token Literal Expected

Hi,

I'm trying to create a custom column in my query to concatenate two other columns based on some criteria.

 

My data is sensitive, so I'll mod a little the names, but essentially I have something like this:

 

Model Serial Number Desired Column
Model 1 1 10000001
Model 1 2 10000002
Model 1 150 10000150
Model 1 72 10000072
Model 2 1 20000001
Model 2 10 20000010

 

I have treid bringind Serial Number as text to preserve the zeros, but it's no good.

I have the Serial Numbers going up to 3 digits, so the amount of zeros may change, so this is the formula I'm using:

=if

Text.From([Model])="Model 1" then "100"

else "200"

&

if

Text.Length([Serial Number]) = 1 then "0000" & Text.From([Serial Number])

else

if

Text.Length([Serial Number]) = 2 then "000" & Text.From([Serial Number])

else

"00" & Text.From([Serial Number])

 

So I'm creating a condition to bring me the Model prefix, and concatenating with a second condition to bring the serial number with the appropriate amount of zeros.

The editor is showing Token Literal Expected in the 'xt' highlighted in red above. I'm completely lost on what should be the right syntax.

Need Help

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    5 years ago

    See if this code works for you

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s1PSc1RMFTSUQJjAzAwVIrVQZYyQkgZoUkZmhrAJEFMVElzhEZzZI1GUOuMMK2D6IDLAZmxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Model = _t, #"Serial Number" = _t, #"Desired Column" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Text.At([Model],Text.Length([Model])-1) & Text.PadStart([Serial Number],7,"0"))
in
    #"Added Custom"