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 Source txt file name (there could be multiple different tables listed as there may be columns with the same name but we want a different Label and/or type)

COLUMN_NAME = Original column name in the source file

COLUMN_LABEL = The 'new' more user friendly Column Name

DATA_TYPE = The underlying SQL Database column type

PQ_TYPE = The Power Query type I want to convert the column to

Changing the Column Names I have been able to do relatively easily, it's the Data Type transformation I have problems with.

(  Table.RenameColumns(#"Changed Type 3",Table.ToRows(#"Master List"), MissingField.Ignore)  ). The only issue with this is, I had to filter the Master List for table, so in reality, I need to 'filter' the Master List to get the column labels.

 

This is a sample table called 'Property' (VW_BIDATA_PROPERTY in the Master List) 

What I really need it to do is lookup the Column Name in the Master List for the correct table (eg, find 'Property Name' in the COLUMN_LABEL column of the Master List for TABLE_NAME = VW_BIDATA_PROPERTY) and Transform the Column Data Type to the type in the PQ_TYPE column.

So in the example, Property, Property Name would transform to text, Updated On, Created On would transform to datetime, TimeZone would convert to number etc.

 

The purpose of the Master List is each Customer/User will want different Column Labels, they can choose which columns to import, so instead of manually changing every column in 50+ Tables, the Master List is a single source. If they want to change the Label, they can change it in the Master List. 

The same logic applies to the types, so columns have a consistent type across all tables (eg Updated On is datetime in all tables).

 

I understand the Table.TransformColumnTypes, but it's referencing another table that is an issue.

 

Also, the Master List will have ALL possible columns that can be possibly imported, but a User may choose to only import a small subset of those. For example, using the sample above, UDF Char 01 - 10 are all possible columns that they could import, but the User may not use them, so won't import them unnecessarily into the Table, but they will exist in the Master List.

 

I'm hoping someone has a solution for this as I really don't want to have to manually update 50+ tables.

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.

9 Replies

  • KNP's avatar
    KNP
    Icon for Super User rankSuper User
    4 years ago

    I think I can help.

    I do something very similar.

     

    In my case, I have a table in Excel that looks like this...

     

    Then a query (This is called ColumnTypes for later reference) that references that table that looks like this...

    let
  Source = ColumnHeaders,
  //TextToType = Table.TransformColumns(Source,{{"Type", Expression.Evaluate}}),                                                                              
  TextToType = Table.TransformColumns(
    Source,
    {
      {
        "Type",
        each Expression.Evaluate(
          _,
          [
            Currency.Type   = Currency.Type,
            Int64.Type      = Int64.Type,
            Percentage.Type = Percentage.Type
          ]
        )
      }
    }
  ),
  TableToListOfLists = Table.ToRows(TextToType)
in
  TableToListOfLists

     

    The single step in the query that does the type change is this...

    = Table.TransformColumnTypes(previousStep, ColumnTypes)

     

    Hopefully this is a good starting point if not the complete solution.

    Let me know if you need further info.

     

    (I'll try and find the original source of this as it is something I've used from another post or blog)

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks for the suggestion, I'm working on adapting it to a function so it can take in a Table Name as a parameter to filter the Master List to only get the Columns for the table in question.

      Question, how do you handle errors where a Column is missing, either from the Master List or is not used in the main table?

      In an ideal world, if there is a column used in the main table, eg a new Column is added in the export, and there is no match for it in the Master List, it should just ignore it and leave it as is. Vice Versa, the Master List will have all possible columns but the main table may only have a small subsection of them.

      • KNP's avatar
        KNP
        Icon for Super User rankSuper User
        4 years ago

        This may be more in line with what you're thinking...

        Table is the main table. ColumnMapping is the mapping table. fChangeColumnsFromReference is the function that does the grunt work. 

        I've attached this as a PBIX file to save you the time.

         

        This will deal with missing values on either side.

         

        // Table
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc6xCoQwDAbgd+ksR2096y64+QTFIdhydakQM/j4l0Q4ELnlzz98JInRjFAhgWm4IGbKKN07HyzPhBz0gWKWJpoJoa75QXulmDSSyt/Sea/EXZx1bc/zKMRZ8K/zvh3C663b8PpB6JzPbd1v1A2hs3IbDlIo15cv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [C = _t, P = _t, S = _t, renameOther = _t, htasht = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"C", type text}, {"P", type text}, {"S", type number}}),
    RenameColumns = Table.TransformColumnNames( ChangedType, fChangeColumnsFromReference)
in
    RenameColumns

         

        // ColumnMapping
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs4vzSspqlTSUXJWitWJVgooyk8pTS4B8gPA/ODEnNRiIC8YzPMvyUgtAvKKUvMSc1MhPLCqktK0NKB4cXGxUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [new = _t, old = _t]),
    #"Reordered Columns" = Table.ReorderColumns(Source,{"old", "new"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"new", type text}, {"old", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"old", "old"}, {"new", "new"}})
in
    #"Renamed Columns"

         

        // fChangeColumnsFromReference
(ColumnName as text) as text =>
  let
    Source =
      if (List.Contains(Record.FieldNames(#sections[Section1]), "ColumnMapping")) = true then
        ColumnMapping
      else
        null,
    ColumnNewName = try

        if List.Contains(Source[old], ColumnName) = true then
          if Text.Trim(Table.SelectRows(Source, each ([old] = ColumnName)){0}[new]) = "" then
            ColumnName
          else
            Table.SelectRows(Source, each ([old] = ColumnName)){0}[new]
        else
          ColumnName
    otherwise
      ColumnName
  in
    ColumnNewName

         

        Let me know if you have any questions.

        I hope this helps.

         

        [originally sourced from: https://www.biinsight.com/a-power-query-custom-function-to-rename-all-columns-at-once-in-a-table/] edited to get it to work correctly.