none
M-Language: Beginner Question RRS feed

  • Question

  • I am an absolute newbie to M, and I am currently playing around with simple code snippets to understand the syntax of the language. Below is my code. I have 3 input variables (product price, qantity sold and product name) and I want to output all three of them (multiply product price with qantity and display the product name). 

    let
        blue_shirt_price = 25,
        blue_shirt_qty_sold = 10,
        product_name = "Blue Shirt"

    in
        blue_shirt_price * blue_shirt_qty_sold & product_name

    This is the error message I am getting:

    An error occurred in the ‘’ query. Expression.Error: We cannot apply operator & to types Number and Text.
    Details:
        Operator=&
        Left=250
        Right=Blue Shirt

    I couldn't figure out how to properly add my string variable to the IN block.

    Any help is highly appreciated. Thanks!

    Tuesday, February 6, 2018 10:18 AM

Answers

  • You need Text.From in order to concatenate the result of the multiplication with a text value. Just the multiplication by itself doesn't need to be converted to Text (though you can do so, as you did in your second example).

    Here's a simpler example:

    = 123 & "abc" // fails, because you can't concatenate a number with text

    = Text.From(123) & "abc" // works, because 123 is converted to text before being concatenated

    Ehren

    Wednesday, February 7, 2018 5:42 PM
    Owner

All replies

  • Hi Rafael. Try this:

    let
        blue_shirt_price = 25,
        blue_shirt_qty_sold = 10,
        product_name = "Blue Shirt"

    in
        Text.From(blue_shirt_price * blue_shirt_qty_sold) & product_name

    Ehren

    Tuesday, February 6, 2018 9:36 PM
    Owner
  • Hi, thanks. That works.

    Just looked it up in the documentation: "[Text.From]Returns the text representation of a number, date, time, datetime, datetimezone, logical, duration or binary value. If a value is null, Text.From returns null. The optional culture parameter is used to format the text value according to the given culture."

    But then, I do not understand why I do not need Text.From here:

    let
        blue_shirt_price = 25,
        blue_shirt_qty_sold = 10,
        product_name = "Blue Shirt"
    in
        blue_shirt_price * blue_shirt_qty_sold

    My code works with Text.From as well:

    let
        blue_shirt_price = 25,
        blue_shirt_qty_sold = 10,
        product_name = "Blue Shirt"
    in
         Text.From(blue_shirt_price * blue_shirt_qty_sold)

    Long story short, I am trying to understand in which cases I need Text.From and in which I do not need it (Text.From seems to be optional in some cases and mandatory in others ... that's a bit confusing to me).

    Thanks!


    • Edited by Rafael Knuth Wednesday, February 7, 2018 7:39 AM
    Wednesday, February 7, 2018 7:38 AM
  • You need Text.From in order to concatenate the result of the multiplication with a text value. Just the multiplication by itself doesn't need to be converted to Text (though you can do so, as you did in your second example).

    Here's a simpler example:

    = 123 & "abc" // fails, because you can't concatenate a number with text

    = Text.From(123) & "abc" // works, because 123 is converted to text before being concatenated

    Ehren

    Wednesday, February 7, 2018 5:42 PM
    Owner
  • Oh, thanks - that's so obvious! I did not see the answer in plain sight. Thanks!
    Friday, February 9, 2018 11:58 AM
  • I was using this to combine a Calculated Column and another column, Coukd you please share how to combine columns if one of the column is calculated column as source
    Friday, November 1, 2019 2:00 AM
  • Hi Varun. In Power Query, combining columns should work the same regardless of whether the source columns are calculated or not.

    Ehren

    Friday, November 1, 2019 6:25 PM
    Owner