Load files from folder with a custom query with power BI

Question

I am trying to load csv files from a folder but I need to apply several custom steps to each file, including dropping the PromoteHeaders default.

I have a custom query that can load a single file successfully. How do I turn it into a query that loads all files in a folder?

By default, File.folder's "promoteHeaders" messes up my data because of a missing column name (which my custom query fixes).

Create a function and apply that function for each file. The function should handle all the transformation and only return the parsed table so you can expand. — mxix
@mxix I figured it is what I needed to do but was not able to find documentation. Could you please point me in the right direction? Thx. — JohannaB

mxix mxix · Accepted Answer · 2019-03-04T16:27:49

The easiest way to create a function that reads a specific template of file is to actually do it. Just create the M to read it and by right click on the entity transform it to a function.

After that is really simple to transform your M so it uses parameters.

You can create a blank query and replace the code with this on as an example, customize with more steps to deal with your file requirements.

= (myFile) => let
        Source = Csv.Document(myFile,[Delimiter=",", Columns=33, Encoding=1252, QuoteStyle=QuoteStyle.None])
    in
        Source

And then Invoke Custom Function for each file with the content as the parameter.

Load files from folder with a custom query with power BI

1 Answers