PQ - Declaring Data Type in Defined Data Table

[johnny51981]

I use Defined Data Tables in my Power Query Excel files so that I can updated the column headers to different names (they aren't uniform across sources). Like so:

I bring it into Power Query Editor, M Code Magic, wham bam, I have consistent column header names. Wonderful.

My ask: is it possible to declare the Data Types that will be used in the Power Query from this same table? And if so, how would the M Code be written to apply these types in the Changed Type step?
The updated table would look something like this:

 

[GraH]

Hi,
I'd say it is possible.

To changes data types, the signature in M is a list of list, where the inner list are {"column name", type}.
You can create this list of inner lists with List.Zip({TableNameOfDefinitionTable[ColColNames, TableNameOfDefinitionTable[ColColTypes]}).

 

[GraH]

Hi johnny51981

Hi,
I'd say it is possible.

To changes data types, the signature in M is a list of list, where the inner list are {"column name", type}.
You can create this list of inner lists with List.Zip({TableNameOfDefinitionTable[ColColNames, TableNameOfDefinitionTable[ColColTypes]}).

So I tried my own dog food and I'm failing. If I find some time today, I might give it another go. Duty calling now though...

 

[sadath]

Dynamic Datatypes

 

[GraH]

I did it slightly different then in the video, without needing to create an example, still I feel there could be a shorter way.
The code below is on some data I used to understand some of the M-code and how it behaves and to test the technique mentioned by @johnny51981.

My sample data table

Col1	Col2	Col3	Col4	Col5	Col6	Col7
CON-5487	12,5	17/01/2023	10:24	5	FALSE
CON-8745	8,45	5/09/2023	15:39	4	TRUE
CON-2658	0,29	20/12/2023	0:57	3	FALSE
CON-3571	80,73	6/01/2024	21:02	4	FALSE	Work in progress

My definition table

Orginal Column	New column	Type
Col1	Order Reference	text
Col2	Amount	number
Col3	Date of Purchase	date
Col4	Time Order In	time
Col5	Number of Items	integer
Col6	Returned	boolean
Col7	Free comment	text
Order Number	Order Reference	text
Amount	Amount	number
Total	Amount	number

Power Query to define the data type

let
    Source = tbl_definition,
    Define_types = Table.AddColumn(Source, "TypeCol", each if [Type] = "text" then Type.ListItem(type {text})
else if [Type] = "number" then Type.ListItem(type {number})
else if [Type] = "integer" then Type.ListItem(type {Int64.Type})
else if [Type] = "date" then Type.ListItem(type {date})
else if [Type] = "time" then Type.ListItem(type {time})
else if [Type] = "datetime" then Type.ListItem(type {datetime})
else if [Type] = "duration" then Type.ListItem(type {duration})
else if [Type] = "boolean" then Type.ListItem(type {logical})
else Type.ListItem(type {any})),
    Keep_col_name_with_type = Table.RemoveColumns(Define_types,{"Orginal Column", "Type"}),
    Create_list_of_lists = Table.ToRows(Keep_col_name_with_type),
    remove_duplicates = List.Distinct(Create_list_of_lists)
in
    remove_duplicates

Power Query to apply the definition table on the data

let
    Source = Excel.CurrentWorkbook(){[Name="raw"]}[Content],
    Rename_with_query_renames = Table.RenameColumns(Source,Renames, MissingField.Ignore),
    Set_types_with_query_typing = Table.TransformColumnTypes(Rename_with_query_renames, Typing)
in
    Set_types_with_query_typing

 

[RoryA]

Could you not use something like:

Expression.Evaluate("type " & [Type])

 

[GraH]

Tried that before and it failed.
I must be doing something wrong with Expression.Evaluate. don't see what.

 

[RoryA]

It wouldn't work with integer of course (since you need Int64.type not 'type integer') but should do with others.

 

[RoryA]

Something like:

Table.AddColumn(Source, "TypeCol", each try Expression.Evaluate("type " & [Type]) otherwise if [Type] = "integer" then Int64.Type else Logical.Type)

 

[johnny51981]

Dynamic Datatypes

This got me to where I needed to be. Appreciate it all!