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

[Anonymous]

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.

[KNP]

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]

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]

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]

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
old	new	old	new	old	new
C	Country	C	Countries	C	Country 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

[KNP]

Sorry, I'm short on time, late Friday afternoon and wife is on my case coz we're heading out 🤣, but I'm really keen to solve this.

Yeah, that had me for a while too, I understand it the same as you've interpreted it.

I think the key to making a completely dynamic solution is to identify the table name of the current query, that's the part I'm stuck on at the moment.

It's interesting to see what this does

let
    Source = #sections,
    Section1 = Record.FieldNames(Source[Section1]),
    #"Converted to Table" = Table.FromList(Section1, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

Paste it into a blank query.

Reordering the tables in the query editor and refreshing this causes the order to change.

The part that needs to be updated is something like...

[this is faux code, not tested and won't work]

 if List.Contains(Source[old], ColumnName) and List.Contains(Source[table], TableName) = true then
          if Text.Trim(Table.SelectRows(Source, each ([old] = ColumnName) and [table] = Source[table]){0}[new]) = "" then

I will have another look the next chance I get.

Best of luck.

There's a chance that converting this solution is over complicating things.

Just in case they have time I will try and summon the wonderful @ImkeF or @MarcelBeug for their expertise. They probably already have a function for it.

[ImkeF · Accepted solution]

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]

@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.

[KNP]

Thanks @ImkeF - you always make that look so simple. I look forward to the blog.

Question - can you see a way to make the TableName dynamic? e.g.

= fnTransformFromMappingTable( Source, "Table2" )

 the "Table2" to populate based on the query that the function is invoked in.

Soheil's function uses 

let
    Source = #sections,
    Section1 = Record.FieldNames(Source[Section1])
...
...

to identify if a table exists, I wonder, is it possible to use a variation of this (or something else) to identify the current query?

[ImkeF]

Hi @KNP ,

I'm not aware of such a functionality.

Stopped playing with #sections a while ago, as it didn't refresh in the service reliably back then.