Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
davidz106
Helper II
Helper II

Get data from folder and append

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.

1 ACCEPTED SOLUTION
rohit_singh
Solution Sage
Solution Sage

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

rohit_singh_0-1655213366475.png

 

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

rohit_singh_1-1655213601326.png

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

rohit_singh_3-1655213750517.png

4) Select the custom function and the column. 

 

rohit_singh_4-1655213878703.png

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! 🙂

 





View solution in original post

3 REPLIES 3
davidz106
Helper II
Helper II

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:/..."

 

 

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)

rohit_singh_1-1655221656293.png

 

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
Solution Sage
Solution Sage

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

rohit_singh_0-1655213366475.png

 

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

rohit_singh_1-1655213601326.png

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

rohit_singh_3-1655213750517.png

4) Select the custom function and the column. 

 

rohit_singh_4-1655213878703.png

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! 🙂

 





Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors