Q: Transform columns based on other table?

Question

My use-case is importing a CSV data table. The imported table has by default type 'text' for all columns. Then transform each column based on a separate transformation table.

CSVTable

'Key'   'Name'   'Creation Date'
-------------------------------------------
'1'     'Bob'    '21/Jan/20 12:00 AM'

TransformationTable

'Field'          'Directive'
-------------------------------------
'Key'            'int64'
'Name'           'text'
'Creation Date'  'date'

My objective is to transform the CSVTable based on the directive in the TransformationTable.
NOTE: 'Name' field is already in text format in the CSV, so no need to transform.

OutputTable

Currently I have hard-coded the transformation in below code line. This snippet

OutputTable = Table.TransformColumnTypes(CSVTable,{{"Key", Int64.Type}, {"Creation Date", type datetime}}),

Question

I want to control the transformation based on the definitions in TransformationTable, instead of hard-coded. How do I achieve that?

Ricardo Diaz · Accepted Answer · 2020-02-25 14:40:16Z

2

Credits to Imke and Marcel

Based on this setup:

Column types table name: TableTypes
Values table name: Table1

Source code:

Table: TableTypes

let
    Source= Excel.CurrentWorkbook(){[Name="TableTypes"]}[Content],
    ChangeColTypes = Table.TransformColumnTypes(Source,{{"Column header", type text}, {"Type", type text}}),
    ToType = Table.TransformColumns(ChangeColTypes,{{"Type", Expression.Evaluate}}),
    ToField = Table.AddColumn(ToType, "Custom", each Record.FieldValues(_)),
    RemoveOtherCols = Table.SelectColumns(ToField,{"Custom"}),
    ToList = RemoveOtherCols[Custom]
in
    ToList

Table: Table1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ChangeTypes = Table.TransformColumnTypes(Source,TableTypes)
in
    ChangeTypes

Output:

If you really need the non-primitive types (like Int64.Type) use:

= Table.TransformColumns(types, {{"col Type", each Expression.Evaluate(_, [Currency.Type=Currency.Type, Int64.Type=Int64.Type, Percentage.Type=Percentage.Type]) }})

answered Feb 25, 2020 at 14:40

Ricardo Diaz

5,7262 gold badges24 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jimmy_1969 Over a year ago

Thanks for your answer. It provided some good insights on how to bridge the two tables, and how to transform non-primitives. Much appreciated!

horseyride · Accepted Answer · 2020-02-25 15:01:59Z

I'll start off by saying I don't think it is possible, because you have the Int64 type in there. I believe you can only change programmatically to these Primitive Types

type null, which classifies the null value
type logical, which classifies the values true and false
type number, which classifies number values
type time, which classifies time values
type date, which classifies date values
type datetime, which classifies datetime values
type datetimezone, which classifies datetimezone values
type duration, which classifies duration values
type text, which classifies text values
type binary, which classifies binary values
type type, which classifies type values.
type list, which classifies list values
type record, which classifies record values
type table, which classifies table values
type function, which classifies function values
type anynonnull, which classifies all values excluding null

That said, assuming you had TransformationTable and CSVTable, this code would (a) list columns in CSVTable (b) Merge the transformation table (c) Expand the merge (d) pull out just the type column (e) add prefix (f) evaluate the expression result to make the result usable (g) use List.Zip to create a paired set of lists of column and type (h) rename based on the List.Zip

let Source = CSVTable,
//List of column names
Columns=Table.ColumnNames(Source),
// Merge in Transformation table to get types for each column
#"Merged Queries" = Table.NestedJoin(Table.FromList(Columns),{"Column1"},TransformationTable,{"Field"},"TransformationTable",JoinKind.LeftOuter),
#"Expanded TransformationTable" = Table.ExpandTableColumn(#"Merged Queries", "TransformationTable", {"Directive"}, {"Directive"}),
// Pull just the column with types in it
Directives=Table.Column(#"Expanded TransformationTable","Directive"),
// Add a prefix and evaluate the expression to make it usable
Directives2 = List.Transform(Directives, each Expression.Evaluate("type " & _)),
// Convert the column names and types to a paired list
TransformList = List.Zip({Columns, Directives2}),
// Convert the column names to the types
TypeTable= Table.TransformColumnTypes(Source, TransformList)
in TypeTable

Thanks for your answer. Your solution really helped me bridge the gap of how to merge my two tables. Much appreciated!

Collectives™ on Stack Overflow

Q: Transform columns based on other table?

CSVTable

TransformationTable

OutputTable

Question

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

CSVTable

TransformationTable

OutputTable

Question

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related