Assign data type to each column, based on a row with defined data types

[Taski97]

Hi all,

I am trying to find a way of defining each column's data type based on what is in the third row. E.g. ID = Text, PA = Text, 2DP = Decimal, DT = Date etc.  I have many tables like this with +50 columns in each so manually changing each column is not feasible. Any help would be greatly appreaciated.

[Peter_Beck]

Hi -

Interesting puzzle! Here is one possible solution.

First, I created a function that will return a list, consisting of the name of a column, and the type I want to change it to (I called it fn_ReturnType)

(colname as text, coltype as text) => if coltype = "ID" then
{colname, type text}
else
if coltype = "PA" then
{colname, type text}
else
if coltype = "2DP" then
{colname, type number}
else
if coltype = "DT" then
{colname, type date}
else
if coltype = "Type" then
{colname, type text}
else false

Now I run the following script (it contains some mocked-up data similar to yours):

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCqksSFXSUfJ0ARJGLgFwMsBRKVYnWsnFMcQRyHN0MjQyNjEFsgxAWM8ISIZkZBYrAFFxfm6qQklqRQmyBmcXYxMjU2OwYhBpqGdkQkiPqxtIjyFUNcgySz0LcwskbYk5xfnIemMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t]),
MetadataRow = Record.ToList(MyData{0}), // I create a list of the row that contains the "type"
Columns = Table.ColumnNames(MyData), // I create a list of the column names
RemvovedRowTable = Table.RemoveFirstN(Source,1), // I remove the row that contains just the type
ColumnChanges = List.Generate(()=>[x=List.Count(MetadataRow), y=0, z=fn_ReturnType(Columns{y}, MetadataRow{y})],
each [x]>0,
each [x = [x]-1, y=[y]+1, z=fn_ReturnType(Columns{y}, MetadataRow{y})],
each [z]),
ChangedCol = Table.TransformColumnTypes(RemvovedRowTable,ColumnChanges)
in

ChangedCol

The "meat" of the script is really this section here:

ColumnChanges = List.Generate(()=>[x=List.Count(MetadataRow), y=0, z=fn_ReturnType(Columns{y}, MetadataRow{y})],
each [x]>0,
each [x = [x]-1, y=[y]+1, z=fn_ReturnType(Columns{y}, MetadataRow{y})],
each [z])

This calls the List.Generate function. It iterates for as many items are in the MetadataRow list. For each item in the list, it calls my fn_ReturnType function, which has the parameter of each column and each type the column should be changed to. It builds out a new "list of lists", where each sub-list is a pair of column name and new data type.

I then apply the "list of lists" to the table, and it changes the column types according to the results of the function.

Copy and paste the function above into one query, and then the main script into another and you will see how it works. You can incorporate them in the same script, but I like to keep my functions separate so I can test them more easily,

You may need to tweak it a bit for your data.

Hope this helps!

Peter