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 🙂

 

Comment (1) 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

Leave a Reply