cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jmdh
Advocate III
Advocate III

change column type programmatically

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

1 ACCEPTED SOLUTION
MarcelBeug
Community Champion
Community Champion

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"
Specializing in Power Query Formula Language (M)

View solution in original post

20 REPLIES 20
MarcelBeug
Community Champion
Community Champion

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.

Specializing in Power Query Formula Language (M)

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.

 

Screenshot (4).png

MarcelBeug
Community Champion
Community Champion

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"
Specializing in Power Query Formula Language (M)

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
Community Champion
Community Champion

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.

Specializing in Power Query Formula Language (M)

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.

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
Community Champion
Community Champion

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.

Specializing in Power Query Formula Language (M)

Marcel,

It flies!

Many many thanks from Paris.

jmdh

Anonymous
Not applicable

nice solution. Thank you @MarcelBeug for this one

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

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous
Not applicable

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.

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

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous
Not applicable

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.

 

 

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

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

MarcelBeug
Community Champion
Community Champion

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" Smiley Wink  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...  Smiley Tongue

Specializing in Power Query Formula Language (M)

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

Yes, stay queryious 🙂

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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
Not applicable

@jmdh

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

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

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 25

Ted's Dev Camp - August 25, 2022

Watch Session 25 of Ted's Dev Camp.

Top Solution Authors