Dynamically loading multiple files from ADLS

[abhay03]

I have a situation where I have a file (File0.csv) connected to ADLS which contains the path of other files that need to be loaded in Power BI.

I have created a custom column that concatenates the fields and creates the complete URL.

File0.csv:

URI	folder	filename	URL
https://xxxxxx.azuredatalakestore.net/webhdfs/v1/	data	file1.csv	https://xxxxxx.azuredatalakestore.net/webhdfs/v1/data/file1.csv
https://xxxxxx.azuredatalakestore.net/webhdfs/v1/	data	file2.csv	https://xxxxxx.azuredatalakestore.net/webhdfs/v1/data/file2.csv
https://xxxxxx.azuredatalakestore.net/webhdfs/v1/	data	file3.csv	https://xxxxxx.azuredatalakestore.net/webhdfs/v1/data/file3.csv
https://xxxxxx.azuredatalakestore.net/webhdfs/v1/	data	file4.csv	https://xxxxxx.azuredatalakestore.net/webhdfs/v1/data/file4.csv
https://xxxxxx.azuredatalakestore.net/webhdfs/v1/	output	file1.csv	https://xxxxxx.azuredatalakestore.net/webhdfs/v1/output/file1.csv
https://xxxxxx.azuredatalakestore.net/webhdfs/v1/	output	file2.csv	https://xxxxxx.azuredatalakestore.net/webhdfs/v1/output/file2.csv
https://xxxxxx.azuredatalakestore.net/webhdfs/v1/	output	file3.csv	https://xxxxxx.azuredatalakestore.net/webhdfs/v1/output/file4.csv
https://xxxxxx.azuredatalakestore.net/webhdfs/v1/	output	file4.csv	https://xxxxxx.azuredatalakestore.net/webhdfs/v1/output/file4.csv

How can I achieve loading other files into Power BI space dynamically by URL using a custom function or power query?

[v-yuezhe-msft · Accepted solution]

@abhay03,

You can firstly connect to https://xxxxxx.azuredatalakestore.net/webhdfs/v1/data folder using ADLS connector in Power BI Desktop following the guide in this blog, then define a parameter in the code in Advanced Editor of your imported query and replace the folder name with in the URL(https://xxxxxx.azuredatalakestore.net/webhdfs/v1/data) with the parameter , and add custom column to get data from all files, which is described in this similar blog: https://www.mattmasson.com/2014/11/iterating-over-multiple-pages-of-web-data-using-power-query/ .

If you have any questions about the code in Advanced Editor, please post a screenshot about your current status and error message.


Regards,
Lydia

[catch93]

I need some help on making this work for our azure subscription.

When I tried to view a single file in the directory of the ADLS, it does not even show the fields on the csv file

And secondly , the example below shows the capability from a webpage, do you have a more recent example actually using the ADLS structure of files created