none
Passing a list of typeTransformations into the Table.TransformColumnTypes function RRS feed

  • Question

  • Updated: 

    As I made a typo in my original I am updating my question and also clarifying.

    I am trying to pass parameters into the Table.TransformColumnTypes function.  I have something like: 

    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",ColumnTypes)

    I therefore need to define the variable ColumnTypes which should be a list of typeTransformations.  I could do this by hard coding the instruction:

    ColumnTypes = {{"end_value", type number}, {"instrument_name", type text}, {"asset_class", type text}}

    My question is, what is the best way of constructing a list of typeTransformations, without needing to hard code it, so I can pass it into the Table.TransformColumnTypes function.  I have tried constructing the equivalent string and constructing a list of strings but neither works.

    Original query:

    What is the best way to pass a list of typeTransformations into the Table.TransformColumnTypes function?

    In the documentation for this function, it refers to a List of typeTransformations.  However, typeTransformation does not seem to be defined.  How can I create a list of typeTransformations to pass into this function?

    I have a string defined in the form {"end_value", type number}, {"instrument_name", type string}, {"asset_class", type string}.  The function will not accept it as a string.

    Thank you

    Duncan


    • Edited by Duncan F Jones Sunday, May 6, 2018 2:09 PM Correcting typo and clarification
    Friday, May 4, 2018 1:54 PM

Answers

  • Duncan,

    It just occurred to me that you're converting text to type and not the other way around. Therefore you don't need to evaluate any 'if' expressions. You can simplify your function as:

    (TypeAsText as text) as type =>
    Expression.Evaluate("type " & TypeAsText)

    Tuesday, May 8, 2018 1:33 AM
  • in order to create a list from your string you need to change the code in following way:

    = {{"end_value", type number}, {"instrument_name", type text}, {"asset_class", type text}}

    this needs to be a separate step/column, that can then be referenced by Table.TransformColumnTypes

    creating a column/step like that will return a list consisting of 3 nested lists (column names & types)

    in general

    = {}

    creates empty list

    Friday, May 4, 2018 4:28 PM

All replies

  • This function accepts a list of transformations in the next form:

    {{"column1", type}, {"column2", type}, ...}

    where type can be one of the primitive data types in Power Query:

    any 
    binary 
    date
    datetime
    datetimezone
    duration
    function
    list
    logical
    none
    null
    number
    record
    table
    text
    time
    type

    Not sure about the use of  null and none types, but there is definitely no type string, but type text :)


    Maxim Zelensky Excel Inside

    Friday, May 4, 2018 3:26 PM
  • Maxim:

    Table.TransformColumnTypes does not recognize the following types: record, function, table, list, null, none, and type. In addition to the remaining types that you list, it will accept Int64.Type, Percentage.Type, and Currency.Type.

    Duncan:

    Check out the language specification documentation available at the following link. The doc explains the fundamentals of the M language, including the types that are valid.

    https://msdn.microsoft.com/en-us/query-bi/m/power-query-m-language-specification


    Edit: Please excuse the finger slip in proposing as answer. I blame the trackpad on my notebook, as I meant to hit the edit button to add an additional sentence to my response to Maxim. :)
    Friday, May 4, 2018 4:07 PM
  • in order to create a list from your string you need to change the code in following way:

    = {{"end_value", type number}, {"instrument_name", type text}, {"asset_class", type text}}

    this needs to be a separate step/column, that can then be referenced by Table.TransformColumnTypes

    creating a column/step like that will return a list consisting of 3 nested lists (column names & types)

    in general

    = {}

    creates empty list

    Friday, May 4, 2018 4:28 PM
  • Hi Colin

    Thank you for your response and apologies for my typo of putting "string" not "text" - too many different languages on one day!

    As you suggest the following works:

    ....

    ColumnTypes = {{"end_value", type number}, {"instrument_name", type text}, {"asset_class", type text}},

    ...

    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",ColumnTypes)

    ...

    But this would require hard coding the columns and types in the first line.  I originally tried just constructing this as a string and passing it in but this did not work.  I also built a getList function and tried something like:

    ColumnTypes = getList("Valuation_Columns_Table", "ColumnType"),

    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",ColumnTypes)

    My getList function appears to work and returns a list with three elements:

    • {"end_value", type number}
    • {"instrument_name", type text}
    • {"asset_class", type text}

    But each of these elements are strings and it gives me the following error:

    "Expression.Error: The column '{"end_value", type number}' of the table wasn't found.
    Details:
        {"end_value", type number}"

    I am trying to work out how to convert something from a string into a typeTransformation or how to build a typeTransformation.

    Sunday, May 6, 2018 1:59 PM
  • Hi Duncan,

    If your goal is to create a dynamic, non-hardcoded list of column name/column type pairs (e.g. to accommodate varying column names or number of columns, then the Table_ConvertColumnTypes custom function discussed in a recent thread should help:

    https://social.technet.microsoft.com/Forums/en-US/ee911661-6cb1-48ac-ae46-d70979b35cb7/homogeneous-list-types-in-m?forum=powerquery

    The function is buried in the discussion, and I apologize for that.

    Sunday, May 6, 2018 3:16 PM
  • Colin

    Thanks for sharing, this was an interesting discussion.  I wanted the users to define the type and not infer it from the data.  However,  I wanted them to be able to do this outside of the query because my users are scared by queries.  I have fixed my problem with the following inelegant solution.  If there is a neater way to do this I would still be interested.

    I created a function listOfTypes which was defined as:

    (TableName as text, ColumnNames as text, ColumnTypes as text) =>
        List.Zip({getList(TableName,ColumnNames),List.Transform(getList(TableName,ColumnTypes),typeFromText)})

    This used two other functions:

    getList was defined as:

    (rangeName, columnName) =>
        Table.Column(Excel.CurrentWorkbook(){[Name=rangeName]}[Content], columnName)

    typeFromText was defined as:

    (TypeAsText as text) as type =>
        if TypeAsText = "number" then type number
        else if TypeAsText = "date" then type date
        else if TypeAsText = "datetime" then type datetime
        else if TypeAsText = "time" then type time
        else if TypeAsText = "date" then type date
        else type text

    Then in my final query I used:

    ColumnTypes = listOfTypes(TableName & "_Columns_Table", "Column", "Type")

    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",ColumnTypes)

    If there is a neater way to do this I would still be interested.

    Duncan

    Sunday, May 6, 2018 5:20 PM
  • Hi Duncan,

    What you've done is fine. In fact, there are 'type from text' implementations at large with 20+ 'if' expressions. I'm not a fan of so many 'if' expressions because of the repetition of the left side of the predicate in each 'if' evaluation. I've created a custom switch function to take care of situations like this, and many additional scenarios (similar to Visual Basic's Select/Case). 

    However, I wouldn't worry too much about the 'ifs' in your current scenario. Believe it or not, but M is the only language on the planet without a switch function, or a switch expression, or the pattern matching capabilities that you will find in every commercial functional language.


    Sunday, May 6, 2018 9:09 PM
  • Hi Duncan,

    you can find one way for a generic Switch-function in M here: http://www.thebiccountant.com/2018/02/15/generic-switch-function-query-editor-in-power-bi-and-power-query/ 


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Monday, May 7, 2018 8:52 PM
    Moderator
  • Duncan,

    It just occurred to me that you're converting text to type and not the other way around. Therefore you don't need to evaluate any 'if' expressions. You can simplify your function as:

    (TypeAsText as text) as type =>
    Expression.Evaluate("type " & TypeAsText)

    Tuesday, May 8, 2018 1:33 AM
  • Maxim:

    Table.TransformColumnTypes does not recognize the following types: record, function, table, list, null, none, and type. In addition to the remaining types that you list, it will accept Int64.Type, Percentage.Type, and Currency.Type.

    Thanks, Colin. Haste makes waste.

    Maxim Zelensky Excel Inside

    Thursday, May 10, 2018 9:55 AM