change column type programmatically

[jmdh]

Can anyone help?

If i have a two col table TABLE1 (with columns Name and Type containing  the col name and its desired col type for an other table, TABLE 2

how can i handle that in advanced editor for the Query related to TABLE 2

ie step x = Table.Transform(Previous step, ********)

Where the ***** are some transformation of Table 1 values  which translate in the right syntax equivalent of "Name", type Y

[MarcelBeug]

Power Query has built in functions to have an example table and apply its type (column names, column types and any keys) to another table:

Table1 = Value.ReplaceType(PreviousStep,Value.Type(Table1))

A silily appracoch to create the example Table1:

let
    Source = {1..10},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Index" = Table.AddIndexColumn(#"Converted to Table", "Index", 0, 1),
    #"Inserted Addition" = Table.AddColumn(#"Added Index", "Inserted Addition", each [Index] + 45000, type number),
    #"Changed Type" = Table.TransformColumnTypes(#"Inserted Addition",{{"Inserted Addition", type date}, {"Column1", Int64.Type}, {"Index", Int64.Type}}),
    Custom1 = #"Changed Type",
    #"Removed Bottom Rows" = Table.RemoveLastN(Custom1,10)
in
    #"Removed Bottom Rows"

Now you can apply the table type of Table1 to Table2:

let
    Source = {1..2},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Duplicated Column" = Table.DuplicateColumn(#"Converted to Table", "Column1", "Column1 - Copy"),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Duplicated Column", "Column1 - Copy", "Column1 - Copy - Copy"),
    Custom1 = Value.ReplaceType(#"Duplicated Column1",Value.Type(Table1))
in
    Custom1

It is also possible to just define table type and apply that, but I guess that's a sttep too far at this moment.

[jmdh]

Thank you for your suggestion.

This would work for me if i could genarate the first table from its description stored, say, in an Excel file.

Since my first post i have tried several approaches:

Basically, this one works: 

 Test2={   {"Sales", type number}, {"Profit", type number}  },
 NextStep=Table.TransformColumnTypes(SalesT_Table, Test2 )

I noticed that Test2 is represented as a List of List .

My main issue is now to be able to get the Test2 value from an external source ie my Excel file (or someting else) where it would be stored as a text string or to directly generate the List of List which represents Test2.

[MarcelBeug · Accepted solution]

OK, that clarifies a bit: so the columns you specify in Table1 are columns that are included in Table2.

Apart from the challenge to change your Table1 to a list with lists, another challenge is to convert text like "type number" from a text to an actual type.

So, it's rather complicated, but the good news is that the following codes accomplishe the tasks. The "TextToType" step converts your textual types to actual types.

Query ColumnSpecs in which the Source step represents your Table1:

let
    Source = #table(type table[col name = text, col type = text],{   {"Sales", "type number"}, {"Profit", "type number"}  }),
    TextToType = Table.TransformColumns(Source,{{"col type", Expression.Evaluate}}),
    FieldValues = Table.AddColumn(TextToType, "Custom", each Record.FieldValues(_)),
    RemovedColumns = Table.RemoveColumns(FieldValues,{"col name", "col type"}),
    TableToList = RemovedColumns[Custom]
in
    TableToList

And Table2:

let
    Source = #table({"Sales","Profit"},{{1000,300},{100,40}}),
    #"Changed Type" = Table.TransformColumnTypes(Source,ColumnSpecs)
in
    #"Changed Type"

[markd]

Hi,

Is it possible to adapt ths solution to work with Table.Group?

I have a couple of tables with a variable number of columns.  Each row is a set of numerical data (counts of process execution) for an hour of the day on a date. There's over 170 columns, one for each of the different processes.  A column only exists in the table if the process was executed.  The two table contain data for the same time period and (mostly) the same processes.  I've appended the two tables together into a single table and want to group by date and hour (first two columns).  I just want to sum (all the other) columns with matching data rows to get the total number of process executions of each type for each hour of each day.

Cheers,

Mark.

[MarcelBeug]

Please create a topic of your own instead of "hijacking" an existing topic that is already solved.

You can always refer and include a link to this topic.

[markd]

Is it possible to adapt this solution to work with Table.Group ??

I've got a couple of tables with a variable number of columns. I want to group by the first couple of columns (date and hour of day) and sum the matching rows in the rest of the columns.

Cheers,

Mark.

[jmdh]

Dear Marcel,

Again thank you.

I am now struggling to achieve the same but instead of type number it is about sorting:

i have create a two column table with FiledtoSort and SortOrder where sort order is Order.Descending, and i cannot seem to make it work.

When i keep only the first column and convert it to a list, say ItemsToSort

using it in Table.Sort( tablename , ItemsToSort) , it works fine.

It is the sort oder with which i struggle...

Jmdh

Many thanks

[MarcelBeug]

Order.Descending and Order.Ascending are just equivalents of the values 1 and 0.

So you can try and change "Order.Descending" by 1 (and any "Order.Ascending" by 0).

Then you can make a list of this table using Table.ToRows.

Now you can use this list as second argument in Table.Sort.

If this doesn't succeed, let me know the details and I'll take a closer look.

[jmdh]

Marcel,

It flies!

Many many thanks from Paris.

jmdh

[Anonymous]

nice solution. Thank you @MarcelBeug for this one

[ImkeF]

Hi Marcel,

that's really smart! Didn't know that we can use Expression.Evaluate like this.

You can shorten the last transformation-steps a bit like this:

let
    Source = #table(type table[col name = text, col type = text],{   {"Sales", "type number"}, {"Profit", "type number"}  }),
    TextToType = Table.TransformColumns(Source,{{"col type", Expression.Evaluate}}),
    TableToListOfLists = Table.ToRows(TextToType)
in
    TableToListOfLists

[Anonymous]

Hi @ImkeF

What would you do in case you want to set the type of a column to a whole number (e.g. Type.Int64)? With the current approach "type number" will always be evaluted as a decimal number.

Are there at the moment maybe other/better methods to define the type of tables columns when this information is provided in text format. In my particular case I receive the information in json-format.

[ImkeF]

It should work to use Type.Int64 instead of type number - have you tried that?

[Anonymous]

Hi @ImkeF,

Yes, I tried that, but I'm unable to succeed.

As an example, let's say I want to reach to following formatted table:

example = #table(type table[
 #"col 1"= number,
 #"col 2"= nullable number,
 #"col 3"= Currency.Type,
 #"col 4"= Int64.Type,
 #"col 5" = Percentage.Type],
 {{1, 1, 1, 1, 1}}
)

Hence I have a table with only text from which I want to derive the type declarations:

types = #table(type table[col Name= text, col Type= text],
{       {"col 1", "type number"},
        {"col 2", "type nullable number"},
        {"col 3", "Currency.Type"},
        {"col 4", "Int64.Type"},
        {"col 5", "Percentage.Type"}
})

When I try to use Expression.Evaluate now, I will got errors in my data which I'm unable to resolve:

= Table.TransformColumns(types, {{"col Type", Expression.Evaluate}})

When using Table.Schema(example), I can see that 'number' represents the Kind of the type, while Int64.Type for example represents the TypeName.

Surprisingly, I was able to correctly define the 'Nullable' property of my type by adding the word "nullable" in my text. So, I guess it should be possible to define all properties when defining a type using the Expression.Evaluate method, only I don't know how at the moment.

[ImkeF]

TBH: Types in M are no fun at all: Seems that only primitive types will work out of the box here.

The solution here is to add a record of the missing types to the Expression.Evaluate-function like this:

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

[MarcelBeug]

Thanks @ImkeF

Well, I didn't know either; I just tried and surprisingly enough it worked!

Also thanks for your shortened code; actually I'm working on an overview of the various (or many) conversions in Power Query,

I still had to cover Table.ToRows; I arrived at "D", which is rather "time consuming"   with all specifics of datetimezones.

As an example, with a datetimezone value as input, Date.From converts to local date and DateTime.Date will just give the date part from the input without conversion to local time.
Completely off topic of course, but that's what you can get if you stay querious... 

[ImkeF]

That was a good off-topic-one ... very much looking forward to your compilation - especially for the optional parameters 😉

Yes, stay queryious 🙂

[jmdh]

Many Thanks!

This is perfect.
I was looking for (but not knowing ) Expression.evaluate which dos the first trick ie to convert a text such as "type number" into a value and also the next one Record.Fieldvalues...

So you saved me a lot (even though i has spent quite a bit of time prior to write my post to the community.

I havr=e now modified the code to access a "live" source and it works fine.

[Anonymous]

@jmdh

Do you want to assign the column type depending on the value written in your table?

[jmdh]

Yes, this is exactly what i want to achieve, ideally for several columns (my first table will have therefore several lines)