Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Dynamically Change Column Type based on Column Name/Type from another table

Hi All   I have project where I need to dynamically Rename Columns and change the Column Types based on a Master List.   This is my current Master List which includes: TABLE_NAME = Original Sour...
Need Help
  • ImkeF's avatar
    ImkeF
    4 years ago

    Hi KNP   ,
    funny, my understanding of Anonymous s request is just about a topic that I intended to blog about for some time (but too busy currently). Done that in some customer projects as well lately and it works really good. I find it especially useful when working with dataflows.
    But I'm attaching the file here.
    The transformations are done using this function:

    (TableToBeTransformed as table, TableName as text) =>
    let
        FilterMatchingTableFromMapping = Table.SelectRows(ColumnMapping, each [Table] = TableName),
        TablesColumnNames = Table.FromColumns({Table.ColumnNames(TableToBeTransformed)}),
        FilterRelevantColumns = Table.NestedJoin(
            FilterMatchingTableFromMapping,
            {"old"},
            TablesColumnNames,
            {"Column1"},
            "TablesColumnNames",
            JoinKind.Inner
        ),
        RenameColumns = Table.RenameColumns(
            TableToBeTransformed,
            List.Transform(Table.ToRecords(FilterRelevantColumns), each {_[old], _[new]})
        ),
        #"Changed Type" = Table.TransformColumnTypes(
            RenameColumns,
            List.Transform(
                Table.ToRecords(FilterRelevantColumns),
                each {_[new], fnReplaceTypes(_[Type])}
            )
        )
    in
        #"Changed Type"

     

    Hope this is what you've been looking for.

ImkeF's avatar
ImkeF
Community Champion
4 years ago

Hi KNP   ,
funny, my understanding of Anonymous s request is just about a topic that I intended to blog about for some time (but too busy currently). Done that in some customer projects as well lately and it works really good. I find it especially useful when working with dataflows.
But I'm attaching the file here.
The transformations are done using this function:

(TableToBeTransformed as table, TableName as text) =>
    let
        FilterMatchingTableFromMapping = Table.SelectRows(ColumnMapping, each [Table] = TableName),
        TablesColumnNames = Table.FromColumns({Table.ColumnNames(TableToBeTransformed)}),
        FilterRelevantColumns = Table.NestedJoin(
            FilterMatchingTableFromMapping,
            {"old"},
            TablesColumnNames,
            {"Column1"},
            "TablesColumnNames",
            JoinKind.Inner
        ),
        RenameColumns = Table.RenameColumns(
            TableToBeTransformed,
            List.Transform(Table.ToRecords(FilterRelevantColumns), each {_[old], _[new]})
        ),
        #"Changed Type" = Table.TransformColumnTypes(
            RenameColumns,
            List.Transform(
                Table.ToRecords(FilterRelevantColumns),
                each {_[new], fnReplaceTypes(_[Type])}
            )
        )
    in
        #"Changed Type"

 

Hope this is what you've been looking for.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

KNP  ImkeF 

 

Thank you both for your assistance on this. I can confirm that the solution provided by ImkeF does everything I was looking for!

 

There were 2 very small changes / tweaks I made:

  1. The fnReplaceTypes were missing Int64.Type and Currency.Type which was a straightforward add to the query and that works
  2. Modified the TableName filter slightly. In my mapping table, while most tables are listed and mapped, I also included an 'ALL' table, where the incoming column name and rename applies to every table. In my project, that was UTC Load Date (Refreshed Date) which is on every table, so rather than mapping it over and over, it filters for the TableName passed in as the parameter AND the ALL table and that works lovely as well 

I've updated my project and so far so good. I just need to publish it to the PBI Service and make sure everything is working.

 

Thank you both again for your assistance, it is much appreciated.