Power Query - Change Type Dynamically

[gelu]

Hello there,

I would appreciate your help on the following.

Detect data type dynamically.

I deal with changing file formats and tables sizes.
When detecting data types I would like to tell PQ to take whatever columns are there and deal with each of them accordingly (instead of hard coded column names as it does by default).

What I do:

1 - I load one file to PQ from folder (there is always just one file but it could be .txt .csv .xls .xlsx or whatever else).

2 - I keep generic column headings when combining the files (by selecting "Do not detect data types"

3 - In the example below the table has 9 columns. But next time it could have 3 or 25 ...

let
    Source = Csv.Document(Parameter1,[Delimiter="#(tab)", Columns=9, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Change Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, _
    {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, _
    {"Column7", type text}, {"Column8", type text}, {"Column9", type text}})
in
    #"Change Type"

How to do this dynamically?

 

[RoryA]

One way:

#"Change Type" = Table.TransformColumnTypes(Source, List.Transform(Table.ColumnNames(Source), each {_, type text}))

 

[gelu]

Dear Rory,

Thank you for your answer. I should have said that what I need is that each column data type must be detected. Right now they all are marked as TEXT.
What needs to be is Dates where the column contains dates, numers where numbers and text where text.

Sorry for not being more to the point the first time.

G

 

[gelu]

There is a solution which works for data loaded from Current Workbook (posted here: TransformColumnTypesDynamically.pq)

But when I load the data from folder that solution fails.

G

 

[RoryA]

How did you apply it? Once you have a table, it doesn't matter how it got into PQ, it's just a table.

 

[gelu]

1 - I tried this:

let
    Source = Csv.Document(Parameter1,[Delimiter="    ", Columns=9, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    Custom1 = TransformColumnTypesDynamically(#"Promoted Headers")
in
    Custom1

2- I invoked it thus:

 

[gelu]

I can solve it in Excel after the table is loaded (But it woul be nicer in PQ)

Function 2 - Dynamic Data Type.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	AGENCY	TRANSACTION_DATE	TRANSACTION_AMOUNT	VENDOR_NAME	MCC_DESCRIPTION	Index
2	District of Columbia Public Schools	3/31/2023	31.18	AMZN MKTP US	BOOK STORES	20521		District of Columbia Public Schools	45016	31.18	AMZN MKTP US	BOOK STORES	20521
3	District of Columbia Public Schools	3/31/2023	65.44	AMZN MKTP US	BOOK STORES	20522		District of Columbia Public Schools	45016	65.44	AMZN MKTP US	BOOK STORES	20522
4	District of Columbia Public Schools	3/31/2023	676.14	AMZN MKTP US	BOOK STORES	20523		District of Columbia Public Schools	45016	676.14	AMZN MKTP US	BOOK STORES	20523
5	District of Columbia Public Schools	3/31/2023	-36.03	AMZN MKTP US	BOOK STORES	20524		District of Columbia Public Schools	45016	-36.03	AMZN MKTP US	BOOK STORES	20524
6	District of Columbia Public Schools	3/31/2023	97.89	AMZN MKTP US	BOOK STORES	20525		District of Columbia Public Schools	45016	97.89	AMZN MKTP US	BOOK STORES	20525
7	District of Columbia Public Schools	3/31/2023	54.55	AMZN MKTP US	BOOK STORES	20526		District of Columbia Public Schools	45016	54.55	AMZN MKTP US	BOOK STORES	20526
8	District of Columbia Public Schools	3/31/2023	32.46	AMZN MKTP US	BOOK STORES	20527		District of Columbia Public Schools	45016	32.46	AMZN MKTP US	BOOK STORES	20527
9	District of Columbia Public Schools	3/31/2023	70.45	AMZN MKTP US	BOOK STORES	20528		District of Columbia Public Schools	45016	70.45	AMZN MKTP US	BOOK STORES	20528
10	District of Columbia Public Schools	3/31/2023	221.25	AMZN MKTP US	BOOK STORES	20529		District of Columbia Public Schools	45016	221.25	AMZN MKTP US	BOOK STORES	20529
11	District of Columbia Public Schools	3/31/2023	101.04	AMZN MKTP US	BOOK STORES	20530		District of Columbia Public Schools	45016	101.04	AMZN MKTP US	BOOK STORES	20530
Sheet1

Cell Formulas
Range	Formula
H2:M11	H2	=IFERROR(IFERROR(DATEVALUE(A2),--A2),A2)

 

[gelu]

(Just in case ... ) Here is the data file: PC Card DAta Q1 22-23.txt

 

[RoryA]

Ah, yes I see the problem. The data is all imported as text, so the column types are all text.

 

[gelu]

Ah, yes I see the problem. The data is all imported as text, so the column types are all text.

Only the data imported from folder does not respond to the function, though.