Forum Discussion
Advocate IVchange column type programmatically
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 TableToListAnd Table2:
let Source = #table({"Sales","Profit"},{{1000,300},{100,40}}), #"Changed Type" = Table.TransformColumnTypes(Source,ColumnSpecs) in #"Changed Type"
Advocate IVThank 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.
Community ChampionOK, 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"- 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- 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" :smileywink: 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... :smileytongue:- ImkeF
That was a good off-topic-one ... very much looking forward to your compilation - especially for the optional parameters ;-)
Yes, stay queryious :-)
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?
- 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.
nice solution. Thank you MarcelBeug for this one
- 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
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.
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.
