topic Re: Allowing schema drift in Gen 2 Flow combining all files in a folder in Dataflow

Allowing schema drift in Gen 2 Flow combining all files in a folder

poinfrettia — Wed, 19 Mar 2025 11:03:59 GMT

Hi there,

I have a Gen 2 Flow which combines all files in a folder. All files contain the same key columns, but not always in the same order - and some files have extra columns thrown in.

The flow is throwing an error when a file with an extra column is included. How can I set it up to allow for this variation in the input files?

Cheers,

Ali

Re: Allowing schema drift in Gen 2 Flow combining all files in a folder

miguel — Wed, 19 Mar 2025 11:41:54 GMT

would you mind sharing the query script (the full code) of your "Transform sample query"?

Re: Allowing schema drift in Gen 2 Flow combining all files in a folder

poinfrettia — Wed, 19 Mar 2025 11:53:14 GMT

Thanks miguel,

Sure - this is the 'Transform Sample Query' step:

let

Source = Csv.Document(Parameter, [Delimiter=",", Columns=22, Encoding=65001, QuoteStyle=QuoteStyle.None]),

#"Removed top rows" = Table.Skip(Source, 1),

#"Promoted headers" = Table.PromoteHeaders(#"Removed top rows", [PromoteAllScalars = true])

#"Promoted headers"

Re: Allowing schema drift in Gen 2 Flow combining all files in a folder

poinfrettia — Wed, 19 Mar 2025 14:53:46 GMT

I have another flow where the columns differ, and it doesn't throw an error - the Gen 2 Flow manages them automatically. The issue must be something else.

Re: Allowing schema drift in Gen 2 Flow combining all files in a folder

coverturfLSH — Wed, 26 Mar 2025 18:17:17 GMT

I know you marked the issue as solved but just throwing my $0.02 out there.

You could expand your columns out "Columns=23" to the widest range of your files.
I wonder if the error you're getting is from a data type mismatch? Like if file one has a text format and file two has a number in the same column?
Are the files random on how you collect if they have the extra columns or not? If they're structured that X files have one format and Y files have another format. You could always do two (or more) separate power queries and clean them up so the columns line up and then append them.