CODEWITHSUNDEEP
excelpowerbitransformationpowerquery
Jimmy_1969
Jimmy_1969

Reputation: 5

Q: Transform columns based on other table?

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?

Upvotes: 0

Views: 469

Answers (2)

horseyride
horseyride

Reputation: 21413

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

Upvotes: 0

Ricardo Diaz
Ricardo Diaz

Reputation: 5696

Credits to Imke and Marcel

Based on this setup:

  1. Column types table name: TableTypes

  2. Values table name: Table1

Setup

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:

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]) }})

Upvotes: 2

Related Questions