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

[poinfrettia]

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

[poinfrettia · Accepted solution]

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. 

[miguel]

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

[poinfrettia]

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])

in

  #"Promoted headers"

[coverturfLSH]

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.