Define Dynamic Data Types in Power Query

- - - - My Courses - - - -
✔️ Mastering DAX in Power BI -

✔️ Power Query Course-

✔️ Master Excel Step by Step-

✔️ Business Intelligence Dashboards-
- - - - Blog - - - -

[bagnon]

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"

[Excel_Balaji]

I have done my 1st Power Query Dynamic . Based on your videos... Thanx a lot.🎉

[williamarthur]

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.

[wayneedmondson]

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!!

[YvesAustin]

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!

[pravinshingadia]

Awesome video. You seem to have solution for all in-depth data analysis issues.

Helped me again today.

[EricHartwigExcelConsulting]

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!

[jerrydellasala]

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.

[ershelin]

Excellent. thanks for the valuable video. one question, currency data type, how to ?

[excel-in-g]

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.

[martingoedhart]

Exactly what I was looking for, thank you.

[delgadojjj]

Hi. I loved it, and I will start using this tip.
Question: How would you do it to have some column as Number and other column as Integer (Int64.Type)?

[danieltoupin]

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

[williamarthur]

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.

[horaciocano]

Great Video!! Thank you very muh.
Do u have any video to make dynamic Groupings based on lists?

[olaayorinde]

Thanks for your video!. Pls can you do a video on "Replace Values" in power query for EXACT word. Thanks

[ashishjain]

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..

[wakeenaushad]

Is there a way to assign date by locale types when we create the type list with the if formula?

[farithahamed]

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

[pseshadri]

Hi Bother could you please explaining with example related RelationShip Dax functions.
Thank in advance for you.