Dynamic & bulk type transformation in Power Query, Power BI and M

This is just a quick code-share-of-the-day of different scenarios for dynamic type transformation of multiple columns at once.

The syntax to transform the format of 2 columns (“Column1” and “Column2”) in a table (“Source”) looks like this:

Now if you would like to do one of the following:

1. Force all columns of the table to be transformed to one type (text)

The second argument of Table.TransformColumnTypes is a list of lists, whose elements contain 2 arguments: The name of the column to transform and the type to be applied.

For this dynamic approach we start with a list of the table’s column names (Table.ColumnNames) and transfer it magically to a list of list, using List.Transform with an expression with curly brackets again like this: each {_, type text}: This operation iterates through every element of the list (List.Transform) and performs the actions that follow the “each” on every element of the list (which is represented by the underscore: _)

2. Transform all newly added columns of a table to one specific type

Imagine you have a table with different column types where users can add new columns with random names. You want these columns automatically to be converted to text:

Same procedure as the first, just that you need to identify the newly added column names. Therefore you use List.Intersect with the two tables to compare in list-format (curly brackets) as shown in line 3 above.

3. Transform all columns whose name are in a list to one specific type

Let’s close with the easiest case, which you’d probably be able to find out by yourself: Say your query returns a dynamic list somewhere with column names who then shall all be converted to a specific type:

You can directly reference the List as the first argument of the List.Transform-command.

You can download the file here: ChgTypeOfColumns.xlsx

Enjoy & stay queryious 🙂

 

Comments (14) Write a comment

  1. Thank you, Imke!
    Here’s something you might be interested in. It demonstrates dynamic table creation ( Column Names, Column Types and values ) via parameter table and recursive function. This WIP lacks defensive code and may not account for ALL primitive types but you’ll get the gist. Just plop in editor and go; it seems to restore original formatting. 🙂 Maybe you have some thoughts on this as well ( last few posts ):

    https://www.powerpivotpro.com/2016/12/how-many-working-days-has-an-employee-been-off-work/

    Many thanks, again.

    let
    Source = Table.FromRecords( {
    [ Root Symbol = “A”],
    [ Root Symbol = “AA” ],
    [ Root Symbol = “AAC” ],
    [ Root Symbol = “AAL” ],
    [ Root Symbol = “AAN” ],
    [ Root Symbol = “AA” ],
    [ Root Symbol = “AAOI” ],
    [ Root Symbol = “AAON” ],
    [ Root Symbol = “AAP” ],
    [ Root Symbol = “AAPL” ]
    } ),

    SymbolRowCount = Number.IntegerDivide( #”Counted Rows”, 20 ) + (if Number.Mod( #”Counted Rows”, 20 ) > 0 then 1 else 0 ),
    PackedSymbolTable_ = Table.FromList({ 1..SymbolRowCount } as list, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #”Counted Rows” = Table.RowCount( Source ),

    #”Added Custom” = Table.AddColumn(PackedSymbolTable_, “PackedSymbols”, each
    let
    FinalList_ = List.Range( Source [Root Symbol], ( [Column1] * 20 – 20 ), 20 ),
    FinalList = Text.Combine( FinalList_, “+” ) as text
    in
    FinalList),

    YAHOO_Tags = Table.FromRecords( {
    [ Tag = “a”, ColumnName = “Ask”, Type = “Currency.Type” ],
    [ Tag = “b”, ColumnName = “Bid”, Type = “Currency.Type” ],
    //[ Tag = “l1”, ColumnName = “Last”, Type = “Currency.Type” ],//
    let
    ColumnsTransformed_ = Table.TransformColumnTypes( Table, { NewColNames { Index },
    if ( YAHOO_Tags [Type] { Index } = “Currency.Type” ) then Type.ListItem( type { Currency.Type } )
    else Type.ListItem( type { text } ) } ),
    ColumnsTransformed = if ( Index

    let
    /* Create an “Error” table with the same schema as a valid quote table */

    /* See Chris’s blog on passing parameters to functions within Function.InvokeAfter(): https://blog.crossjoin.co.uk/2015/04/30/using-function-invokeafter-in-power-query/ */
    Source_ = Function.InvokeAfter( ()=>
    Web.Contents( “http://download.finance.yahoo.com/d/quotes.csv?s=” &PackedSymbolList &”&f=” &PackedTags, [ManualStatusHandling={404}] ),
    #duration( 0, 0, 0, .5 ) ),
    GetMetadata = Value.Metadata(Source_),
    GetResponseStatus = GetMetadata[Response.Status],
    QuoteTable = Csv.Document( Source_, [Delimiter=”,”, Encoding=1252] )
    in
    QuoteTable

    in
    FinalTable

    Reply

  2. Pingback: How to use List.Zip in Power Query – Ivan Bond's blog

  3. After learning several ideas from the material you share around the web, today I managed to get 99% of the way there by myself with a solution for case 1 as mentioned above. Upon reviewing your examples here and applying some quick corrections, my code is working. Now I am moving full speed ahead! Thank you for your contributions around the web Imke! Cheers!

    Reply

  4. Can this be adjusted to match the list of column names we want to change a type to with an associated list of types? Out of 30 columns I have a list of 5 column names and associated types (e.g. column 1 –> Int64.Type, column 2 –> type text and so on. I can see in your last example how to iterate through the first list of column names to be changed but I can’t figure out how to match the column name to the corresponding Type in the other list.

    Reply

    • Yes, this can easily be done as well:
      If you have your transformations in a table with the column names in the first column and the (real) types in the second column, the formula would be this: Table.TransformColumnTypes(Source, Table.ToRows(Transformations))
      But if they are in separate lists, you would have to use List.Zip like so: Table.TransformColumnTypes(Source, List.Zip( { ListWithColumnNames, ListWithTypes } ))

      Reply

      • I have 2 tables with 1 column each. First one with the columnnames, second one with the names of the types. I transfer them to lists like this:

        = Table.TransformColumnTypes(#”Temp1Result”, List.Zip({Table.ToList(#”HeaderNames”), Table.ToList(#”HeaderTypes”)}))

        But I get an error:

        Expression.Error: We cannot convert the value “type text” to type Type.
        Details:
        Value=type text
        Type=Type

        Reply

        • Your table with types mustn’t contain the textual representation of the type, but the type itself.
          Like so for example: #table({“MyTypes”}, {{type text}, {type number}})

          Reply

      • There is any format to change the Text Type to “Type Type”, i’m trying do what EM are doing with diferent tranformations using one table of transformations

        Reply

        • Sorry, but I don’t understand what you’re trying to achieve. Could you please give more details?
          /Imke

          Reply

  5. Hi Imke!

    I’ve implemented this solution and it worked like a charm! Thank you!

    I’ve tried using a similar approach to TransformColumns function and I’m not getting the expected result.

    I have a table to adjust scale for each field of type integer individually (ScaleTable) and I want to dynamically generate the transformation list to TransformColumns function.
    Here’s my code:

    let
    ScaleTable = #table({“Field”, “Scale”},{{“Price”, 2},{“Rate”, 6}}), //ScaleTable example
    ScaleList = Table.ToRows(ScaleTable),

    Source = #table({"Price", "Rate"},{{300, 3000000},{40, 40}}),

    TransformList = List.Transform(ScaleList, each {_{0}, each (Value as number) as number => Value / Number.Power(10, _{1})}),

    ResultTable = Table.TransformColumns(Source, TransformList), //dynamic TransformColumns generate functions instead of its return value

    ExpectedTable = Table.TransformColumns(Source, {{"Price", each _ / Number.Power(10, 2)}, {"Rate", each _ / Number.Power(10, 6)}})

    in
    ExpectedTable

    In the above code, I’d like to adjust the Price for 2 decimal places and the Rate for 6 decimal places. However, ResultTable doesn’t show the function return value… Instead, it shows the function itself! It’s as if the function is not invoked in TransformColumns function.

    Is there any way to handle with it? Or this approach isn’t possible in PQ?

    Reply

    • That’s completely doable.
      Just get rid of one “each” in step “TransformList” like so:
      TransformList = List.Transform(ScaleList, each {_{0}, each (Value as number) as number => Value / Number.Power(10, _{1})}),

      So your step becomes this:

      TransformList = List.Transform(ScaleList, each {_{0}, (Value as number) as number => Value / Number.Power(10, _{1})}),

      Cheers, Imke

      Reply

Leave a Reply

Die Datenschutzbestimmungen finden Sie hier: / Please find the privacy policy here: https://wp.me/P6lgsG-Rz