Reply
Regular Visitor
Posts: 44
Registered: ‎02-02-2017
Accepted Solution

Importing data from folder

Hi All,

 

How to get the detailed data from the different schema based file  , after connecting to the folder in Power BI Desktop.

 

 

 

Thanks,

Pratima


Accepted Solutions
Highlighted
Super Contributor
Posts: 2,326
Registered: ‎08-14-2016

Re: Importing data from folder

Hi @Pratima,

 

>>How to get the detailed data from the different schema based file  , after connecting to the folder in Power BI Desktop.

You can refer to below steps to deal with specific file types which "get data" from folder:

 

1. Write a custom function to analysis different file type.
2. Get Data from folder.
3. Add custom function to invoke aboe custom function.

 

Formula:

 

let
   AnalysisFile = (FilePath as text, Extension as text) => 
    let
        values = {
        {".xlsx", Excel.Workbook(File.Contents(FilePath), true)},
	{".csv", Csv.Document(File.Contents(FilePath))},
	{".txt", Csv.Document(File.Contents(FilePath))},
        {Extension, null}
        },
        Result = List.First(List.Select(values, each _{0}=Extension)){1}
    in
	   Result
in
    AnalysisFile

 

Full query;

let
    Source = Folder.Files("C:\Users\xxxxx\Desktop"),
    #"Filtered Rows" = Table.SelectColumns(Table.AddColumn(Source,"AnalysisData", each AnalysisFile([Folder Path]&[Name],[Extension])),{"Folder Path","Name","AnalysisData"})
in
    #"Filtered Rows"

 

Capture.PNG

 

Regards,

Xiaoxin Sheng

View solution in original post


All Replies
Highlighted
Super Contributor
Posts: 2,326
Registered: ‎08-14-2016

Re: Importing data from folder

Hi @Pratima,

 

>>How to get the detailed data from the different schema based file  , after connecting to the folder in Power BI Desktop.

You can refer to below steps to deal with specific file types which "get data" from folder:

 

1. Write a custom function to analysis different file type.
2. Get Data from folder.
3. Add custom function to invoke aboe custom function.

 

Formula:

 

let
   AnalysisFile = (FilePath as text, Extension as text) => 
    let
        values = {
        {".xlsx", Excel.Workbook(File.Contents(FilePath), true)},
	{".csv", Csv.Document(File.Contents(FilePath))},
	{".txt", Csv.Document(File.Contents(FilePath))},
        {Extension, null}
        },
        Result = List.First(List.Select(values, each _{0}=Extension)){1}
    in
	   Result
in
    AnalysisFile

 

Full query;

let
    Source = Folder.Files("C:\Users\xxxxx\Desktop"),
    #"Filtered Rows" = Table.SelectColumns(Table.AddColumn(Source,"AnalysisData", each AnalysisFile([Folder Path]&[Name],[Extension])),{"Folder Path","Name","AnalysisData"})
in
    #"Filtered Rows"

 

Capture.PNG

 

Regards,

Xiaoxin Sheng