Forum Discussion
Import files with different extension type from the same folder
Hi,
I am new on this community so I hope this is the right place where to ask help for my issue.
My power BI file was set to import .txt files from a folder, however the extension type of some of these files have been changed from .txt to .xlsx; so right now inside the folder there are both .txt and .xlsx files. When I update the Power BI file these .xlxs files are uploaded but completely "blank" (the information within these files is not loaded), so I would like to know if it is possible to import files with different extensions from the same folder or if I have to import them separately.
Thanks in advance
You can modify your Power Query code to use different file ingestion routines (called functions) depending on the extension type of the file. Note that .xlsx files have a totally different structure (and potentially many tabs that you need to choose from) than plain .txt files.
Something like this
let
Source = SharePoint.Files("https://xxx.sharepoint.com/teams/yyy", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".txt" or [Extension] = ".xlsx")),
#"Get File Contents" = Table.AddColumn(#"Filtered Rows", "File", each if [Extension]=".txt" then GetTXT([Content]) else GetXLSX([Content]))in
#"Get File Contents"
2 Replies
You can modify your Power Query code to use different file ingestion routines (called functions) depending on the extension type of the file. Note that .xlsx files have a totally different structure (and potentially many tabs that you need to choose from) than plain .txt files.
Something like this
let
Source = SharePoint.Files("https://xxx.sharepoint.com/teams/yyy", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".txt" or [Extension] = ".xlsx")),
#"Get File Contents" = Table.AddColumn(#"Filtered Rows", "File", each if [Extension]=".txt" then GetTXT([Content]) else GetXLSX([Content]))in
#"Get File Contents"
Davide_V , Import a excel file separately. Go go data transformation/ Edit Query mode. Right-click on the table and open the Advance editor.
Take this code.
Do the same with the text file.
Now compare the code and move the changes in excel source code text source code.
Take care of Path , column name and Type
