I followed these steps to create a custom function that should work to change the type of each column dynamically based on the Value.Type in the first row: (Src as table) => let Custom3 = Table.Transpose(Table.DemoteHeaders(Table.FirstN(Src,1))), #"Added Custom" = Table.AddColumn(Custom3, "Type", each Value.Type([Column2])), RemovedColumns = Table.RemoveColumns(#"Added Custom",{"Column2"}), Types = Table.ToColumns(Table.Transpose(RemovedColumns)), #"Changed Type" = Table.TransformColumnTypes(Src,Types) in #"Changed Type"
Update: Running insider edition of 365, I had to add "if [Type] = "datetime" then #datetime( 2022,12,31,10,0,0)" to the Custom Column of the DataType query.
Another way and again, it has limitations, create a a table of same number of columns as original ie NewType = #table ( {"A","B","C"} , {{1,1,1}} ) and assign data type for each column wrap in ; Value.Type( Table.TransformColumnTypes(Source,{{"A", type text}, {"B", type text}, {"C", type text}} )) Then Value.ReplaceType( Table_to_Change , NewType ) if you have not used the same heading they also change, and when I tried Tab col names, it caused a circular dependency.
Hi Chandeep, Great stuff, as usual, it's getting boring 😛. Seems I missed this old video before, yet it came to the rescue a few days ago when I answered this exact same question on the Mr Excel forum. I decided to tweak your code a bit. At the core it's the same idea, but I'm using Type.ListItem(type {type}). Something of this sort: if [Type] = "text" then Type.ListItem(type {text}) else if [Type] = "number" then Type.ListItem(type {number}) else if [Type] = "integer" then Type.ListItem(type {Int64.Type}) else if [Type] = ... Seems to sort out some of the questions posted before on the difference between number and integer.
Hello Sir, you really Wonderful...I have a query, whenever I modify or update my excel which is source, and refresh in power query all column data converted into text data type, and I have to manually convert them into correct data type as per source excel.please help..
Hi Chandeep. Awesome! Love these list tricks to make queries dynamic. Just recently learned one to dynamically pick which columns are returned by the query. Now this one lets me dynamically define the data types of each column. Great! Thanks for sharing :)) Thumbs up!!
It's not working for me Chandeep. I followed your instructions to the letter but I'm getting an error message: Expression.Error: The column 'Funding date' of the table wasn't found.
had a go first and used list zip ; let cols = Table.ColumnNames( Source ), types = { type date } & { type text } & {Int64.Type} & {Decimal.Type} , replacer = List.Zip( { cols, types } ) in replacer I have found that Value.Type only return 'number' it does not distinguish between whole and decimal, but a great approach. Thank you.
Chandeep, thank you again for a superb demonstration! A quick question: can this method be expanded to other grouping types? I am thinking of dynamically sorting by a number of columns. Since Order.Ascending =0 and Order.Descending=1, can one simply create a two-column list with headers in the fist column and 1/0 in the second column? I will test that to see if it works - [edit]: yep! it works just great!
Hi Chandeep ji, I am trying to apply above fix in a table which contains derived values, that is, such table is not a source table as you have used in the video. Further, the table is dynamic and sometimes, some headers are missing in the data available. In such case, the solution as described in your video given following error - "Expression.Error: We cannot convert the value 1 to type Text. Details: Value=1 Type=[Type]" Can you suggest how to get rid of this error? Very thanks in advance...
Sir, How we avoid breaking queries in PowerQuery especially in the "Changed type" step? MissingField.Ignore is not working in that step. Could you please give me a solution to this? The query will break if we delete a column in the data. Thank you
Well done video. Followed the step by step approach and it solved my problem. Chandeep, I would like to use this DataType table to ensure all my future CSV import get the pre-assign type. Is there a way to make it more universal - meaning that if my new CSV does NOTinclude the HeaderName defined in the table, it woulf still work??? Thank you
Fantastic video! Thank you creating and sharing this! I am going to start incorporating this into my workflow and see what thought experiments I can come up with!
Hello. Thanks for the video. Is there any way to connect with you. I need some inputs from you as I’m preparing a HR Dashboard where I need to achieve to get historical data which is rolling for last 12 months. Can you please support
Hello Very Very good. if do you have a Dynamic column also? Please i am Trying to find an example for Cartesian Product in Power Query. Multiple tables with multiple column in the same workbook, with some equal transformation and some diferents. Any example please? Tks in advance
