Forum Discussion
Dynamically Change Column Type based on Column Name/Type from another table
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.
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.
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.
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:
- The fnReplaceTypes were missing Int64.Type and Currency.Type which was a straightforward add to the query and that works
- 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.
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?