Expression.error with the "RIGHT" formula

[LD1]

Hi everyone, 

I don't get why this error appears with the formula "RIGHT" (just below)

The syntaxe is validated but when i am in the result, i have this error message:

Formula: 

About the column concerned:

(First: year (2021) and next month (05) for example) 

In the cube all data is in text type.

Can you help me please? (Sorry in advance but i work with French application)

Thank you,

LD

[jcalheir · Accepted solution]

Hi

Use the Text.End formula. Try this:

(...) "Numero mois", each Text.End([Mois Affichange], 2), type text)

If you for some reason have troble to find formulas, power query has the option "Column from examples" investigate that, it will help you when you are not so sure on which formula to use

Kind regards,
José
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂

[PaulDBrown · Accepted solution]

If you want to extract the month values, use the option "extract" under transform in the ribbon:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMjQwVYrVgTLNoExDSwNDBNMIwTRGME2UYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Mois = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Mois", Int64.Type}}),
    #"Extracted Last Characters" = Table.TransformColumns(#"Changed Type", {{"Mois", each Text.End(Text.From(_, "es-ES"), 2), type text}})
in
    #"Extracted Last Characters"

If you want to split into year and month, use the Split column option:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMjQwVYrVgTLNoExDSwNDBNMIwTRGME2UYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Mois = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Mois", Int64.Type}}),
    #"Split Column by Position" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type", {{"Mois", type text}}, "es-ES"), "Mois", Splitter.SplitTextByPositions({0, 4}, false), {"Mois.1", "Mois.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Mois.1", Int64.Type}, {"Mois.2", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Mois.1", "Année"}, {"Mois.2", "Mois"}})
in
    #"Renamed Columns"

[LD1]

Wonderful !!

Thank you so much.

[jcalheir · Accepted solution]

Hi

Use the Text.End formula. Try this:

(...) "Numero mois", each Text.End([Mois Affichange], 2), type text)

If you for some reason have troble to find formulas, power query has the option "Column from examples" investigate that, it will help you when you are not so sure on which formula to use

Kind regards,
José
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂