Get data from folder and append

[davidz106]

I hava a complicated .csv that I managed to format as I wish with 20 line of M code.

Is it possible to tell Power BI to watch a folder for new .csv then apply same query on it and only then append it to original file?

Reason I am asking: If I combine files in a first step (via get data from folder) I lose the ability to format the data as I need.

[davidz106]

Thanks for reply.

I get an error however: an error occurred in the "Cmerge" query. DataFormat.Error: The specified file path must be a valid absolute path.

I used:

let parameter = (file_name as text) =>
let
Source = Csv.Document(File.Contents(file_name),[Delimiter=" ", Columns=26, Encoding=1200, QuoteStyle=QuoteStyle.None]),

where file_name used to be an absolute path before that "C:/..."

[rohit_singh]

Hi @davidz106 ,

In that case I think you will have to create an additional column with the full path name.

Navigate to your main csv query, and select the "Name" and "Folder Path" columns. (Please make sure you delete the step "#Removed Other Columns" else you will not be able to see the Folder Path column)

Click on merge columns. You will see that a new column is created. This is the full file path. When you invoke your function, use this column rather than the name column.

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂

[rohit_singh · Accepted solution]

Hi @davidz106 ,

This can be done using a custom function in power query. You will need to create a custom function that contains your 20 line M code. 
Please try to create your solution using the following steps as a guide :

1) Load all csv files from folder

2) Open a blank query and create a function as shown below :

3) In the CSV query, remove extra columns. Then invoke the custom function 

4) Select the custom function and the column. 

What you are doing is taking each file in your staging location and feeding that as an input to your custom function using the file_path variable within the function. The output of the function is transformed data as per your M-code. The transformed data for each file will be added as a column after function invocation. Each row will have transformed data which you can then combine in additional steps.

Hope this helps.

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂