none
Applying Table.TransformColumnTypes to a Column of Tables RRS feed

  • Question

  • Hi All,

    Can anyone tell me if this is possible?  Context:

    • I have a column of tables
    • Each of the tables contain many columns
    • Some of those columns are in text format, but then need to be in date format
    • I am trying to find a way to remedy this by "reaching in" to the table with a Table.TransformColumn... function to do it
    • I have a separate table that lists the column names I want to change, and the types I want to change them to

    The closest I have gotten to an answer is Marcel's answer here:  https://community.powerbi.com/t5/Desktop/change-column-type-programmatically/td-p/140534

    However, I am not successful in applying this method into the column of Tables.  Can anyone advise?  

    The 2nd item below is the List of List that contains the column name and type, per Marcel's article.

    


    Tuesday, September 26, 2017 10:22 PM

Answers

All replies

  • Hi Jake,

    Try something like this:

    = Table.TransformColumns(PreviousStepName, {{"ColumnName", each Table.TransformColumnTypes(_, {{"NestedColumnName", type text}})}})

    This recent article by Chris Webb may also be relevant:

    https://blog.crossjoin.co.uk/2017/09/25/setting-data-types-on-nested-tables-in-m/

    Ehren

    Wednesday, September 27, 2017 9:13 PM
    Owner
  • Hi Jake,

    following up Ehrens suggestion, this is how you can integrate your list with types in a dynamic way in it:

    let
        NestedTables= #table(type table [Column = table], {{#table(type table [Date = text, Number = text], {{"01.01.2017", "1"}})}, {#table(type table [Date = text, Number = text], {{"01.01.2017", "1"}})}}),
        ListWithTypes = {{"Date", type date}, {"Number", type number}},
        TypeRecord = Record.Combine(List.Transform(ListWithTypes, each Table.ToRecords(#table({_{0}}, {{[Type = _{1}, Optional = false]}})){0})),
        Transform = Table.TransformColumns(NestedTables, {{"Column", each Table.TransformColumnTypes(_, ListWithTypes), type table Type.ForRecord(TypeRecord, false)}}),
        Expand = Table.ExpandTableColumn(Transform, "Column", {"Date", "Number"}, {"Date", "Number"})
    in
        Expand
     

    It is a but clumsy with regards to the additional dynamic definition of the complex table type that becomes necessary if you want to have the dedicated types on the columns as well after expansion (if you're interested in that topic: https://social.technet.microsoft.com/Forums/en-US/636e9b44-6820-4ff2-ab60-5dd6a5307bd2/type-conversion-mysteries?forum=powerquery)

    Maybe someone will share a shorter way for it here :)


    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!


    Sunday, October 8, 2017 9:22 AM
    Moderator
  • Following up on Imke's solution: in the query below I added another column to the second nested table in step 1, so the nested tables are not identical. Furthermore, the rename list doesn't include all columns.

    There 2 transform steps, the first step transforms the column types of the nested tables; the second transform step transforms the outer table: i.c. the column with the nested tables, based on a Table.Combine of all nested tables, so all columns are included.

    The last step expands all columns of the nested tables dynamically.

    let
        NestedTables= #table(type table [Column = table], {{#table(type table [Date = text, Number = text], {{"01.01.2017", "1"}})}, {#table(type table [Date = text, Number = text, AnotherColumn = text], {{"01.01.2017", "1", "MarcelBeug"}})}}),
        ListWithTypes = {{"Date", type date}, {"Number", type number}},
        Transform1 = Table.TransformColumns(NestedTables, {{"Column", each Table.TransformColumnTypes(_, ListWithTypes)}}),
        Transform2 = Table.TransformColumns(Transform1, {{"Column", each {_}{0}, Value.Type(Table.Combine(Transform1[Column]))}}),
        Expand = Table.ExpandTableColumn(Transform2, "Column", Table.ColumnNames(Table.Combine(Transform2[Column])))
    in
        Expand

    Sunday, October 8, 2017 1:43 PM
  • Thanks Marcel,

    that code does not look as threatening as mine :)

    (the step Transform2 is also a perfect example for a line of code that should be redundant "under normal circumstances"...)


    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!

    Sunday, October 8, 2017 3:29 PM
    Moderator
  • Thank you Imke, Thank you Marcel,

    After some brow-furrowing, I mostly understand the logic and the steps involved.  It baffles me how much you both know.  Thanks for helping out a newbie. 

    Friday, October 27, 2017 11:07 PM
  • Hi Ehren,

    I employed this in the model... thank you sir for your assistance again!

    Jake

    Friday, October 27, 2017 11:08 PM