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.

KNP's avatar
KNP
Super 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
      Super 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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Thank you for your reply and the attached PBIX.

        This is almost exactly what I need, with one change, in that the fChangeColumnsFromReference needs to have a second parameter to filter the ColumnMapping table by TableName.

        The use case is the incoming column name(s) may all be the same across multiple tables, but the rename value is different. An example, using the sample provided:

        Table1 Table2 Table3 
        oldnewoldnewoldnew
        CCountryCCountriesCCountry of Residence

        In this case, there are 3 tables, each get imported with a column 'C', but the new name for 'C' on each table is different because the tables represent different things.

         

        In your example PBIX, I adapted the ColumnMapping to include a TableName value, which represents which Table does that rename belong to. So far so good.

        Then I edited the fChangeColumnsFromReference to include a second 'TableName' parameter. When invoking this function directly, it works correctly and I get the correct rename value depending on which TableName I specified. Still so far so good.

         

        However the issue I have that I just can't seem to get my head around, is how to edit the Table query calling the function, to pass the TableName parameter in. I'm still trying to grasp how the function, which expects a parameter, doesn't have the parameter specified within the Table Query.

        I'm assuming that in the TransformColumnNames function, it is effectively iterating over each column name and passing the column name to the fChangeColumnsFromReference function.

         

        This is the only step that i'm now stuck on.

         

        For whatever reason it's not letting me attach the revised PBIX, but here are the queries:

        fChangeColumnsFromReference

         

        (TableName as text, ColumnName as text) as text =>
  let
    Source =
      if (List.Contains(Record.FieldNames(#sections[Section1]), "ColumnMappingTest")) = true then
        ColumnMappingTest
      else
        null,
    ColumnNewName = try
        if List.Contains(Table.SelectRows(Source, each ([Table_Name] = TableName))[old], ColumnName) = true then
          if Text.Trim(Table.SelectRows(Table.SelectRows(Source, each ([Table_Name] = TableName)), each ([old] = ColumnName)){0}[new]) = "" then
            ColumnName
          else
            Table.SelectRows(Table.SelectRows(Source, each ([Table_Name] = TableName)), each ([old] = ColumnName)){0}[new]
        else
          ColumnName
    otherwise
      ColumnName
  in
    ColumnNewName

         

         

        Example Table 3 (Country of Residence)

         

        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, fChangeColumnsFromReferenceTest("Table3", each _) )
in
    RenameColumns