We can connect to the .CSV and .XLSX files using the below Power Query methods:
1) Source : SharePoint Files - SharePoint.Files Method:
Parameters:
sp_root_folder = https://sharepoint.com/sites/RootFolder/
sp_target_folder = Shared Documents/General/SubFolder/TargetFolder/
Excel File(.xlsx) :
let
Source = SharePoint.Files(sp_root_folder, [ApiVersion = 15]),
TargetFolder =
Table.SelectRows(Source, each ([Folder Path] = sp_root_folder & sp_target_folder )),
Table.SelectRows(Source, each ([Folder Path] = sp_root_folder & sp_target_folder )),
TargetFile = Table.SelectRows(TargetFolder, each ([Name] = "SourceFile.xlsx")),
SelectContent = Table.SelectColumns(TargetFile,{"Content"}),
Content = SelectContent{0}[Content],
ImportXL = Excel.Workbook(Content){[Item= "Src_Data",Kind="Sheet"]}[Data],
Promote_Headers = Table.PromoteHeaders(ImportXL, [PromoteAllScalars=true])
in
Promote_Headers
Comma Delimited CSV File(.csv) :
let
Source = SharePoint.Files(sp_root_folder, [ApiVersion = 15]),
TargetFolder =
Table.SelectRows(Source, each ([Folder Path] = sp_root_folder & sp_target_folder )),
Table.SelectRows(Source, each ([Folder Path] = sp_root_folder & sp_target_folder )),
TargetFile = Table.SelectRows(TargetFolder, each ([Name] = "SourceFile.csv")),
SelectContent = Table.SelectColumns(TargetFile,{"Content"}),
Content = SelectContent{0}[Content],
Import_CSV =
Csv.Document(Content,[Delimiter=",", Columns=null, Encoding=65001, QuoteStyle=QuoteStyle.Csv]),
Csv.Document(Content,[Delimiter=",", Columns=null, Encoding=65001, QuoteStyle=QuoteStyle.Csv]),
Promote_Headers = Table.PromoteHeaders(Import_CSV, [PromoteAllScalars=true])
in
Promote_Headers
2) Source : OneDrive Files - Web Method:
Parameters:
Parameters:
OneDrive_File_Path =
https://sharepoint.com/personal/userid_domain/Documents/SourceFolder/SourceFile.xlsx
Excel File(.xlsx):
let
Notes: here, Item="SheetName"
Comma Delimited CSV File(.csv) :
3) Source : Local Files - File.Contents Method:
Excel File(.xlsx) :
let
Source =
Excel.Workbook(File.Contents("T:\T_Tech_Lab\PowerBI_Lab\DataSets\ds_Sample.xlsx"), null, true),
tbl_Sales_Sheet = Source{[Item="tbl_Sales",Kind="Sheet"]}[Data],
Promote_Headers = Table.PromoteHeaders(tbl_Sales_Sheet, [PromoteAllScalars=true])
in
Promote_Headers
Comma Delimited CSV File(.csv) :
let
Source =
Csv.Document(File.Contents(""T:\T_Tech_Lab\PowerBI_Lab\DataSets\csv_Input.csv"),[Delimiter=",", Columns=12, Encoding=65001, QuoteStyle=QuoteStyle.None]),
Promote_Headers = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in
Promote_Headers
Tab Delimited Text File(.txt) :
let
Source =
Csv.Document(File.Contents(""T:\T_Tech_Lab\PowerBI_Lab\DataSets\txt_TabDelimited_Input.txt"),[Delimiter="#(tab)", Columns=12, Encoding=1252]),
Promote_Headers = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in
Promote_Headers
Excel File(.xlsx):
let
Source = Excel.Workbook(Web.Contents(OneDrive_File_Path), null, true),
Src_Data = Source{[Item="data",Kind="Sheet"]}[Data],
Promote_Headers = Table.PromoteHeaders(Src_Data, [PromoteAllScalars=true])
in
Promote_Headers
Notes: here, Item="SheetName"
Comma Delimited CSV File(.csv) :
let
Source = Csv.Document(Web.Contents(OneDrive_File_Path),[Delimiter=",", Encoding=65001]),
Promote_Headers = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in
Promote_Headers
3) Source : Local Files - File.Contents Method:
Excel File(.xlsx) :
let
Source =
Excel.Workbook(File.Contents("T:\T_Tech_Lab\PowerBI_Lab\DataSets\ds_Sample.xlsx"), null, true),
tbl_Sales_Sheet = Source{[Item="tbl_Sales",Kind="Sheet"]}[Data],
Promote_Headers = Table.PromoteHeaders(tbl_Sales_Sheet, [PromoteAllScalars=true])
in
Promote_Headers
Comma Delimited CSV File(.csv) :
let
Source =
Csv.Document(File.Contents(""T:\T_Tech_Lab\PowerBI_Lab\DataSets\csv_Input.csv"),[Delimiter=",", Columns=12, Encoding=65001, QuoteStyle=QuoteStyle.None]),
Promote_Headers = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in
Promote_Headers
Tab Delimited Text File(.txt) :
let
Source =
Csv.Document(File.Contents(""T:\T_Tech_Lab\PowerBI_Lab\DataSets\txt_TabDelimited_Input.txt"),[Delimiter="#(tab)", Columns=12, Encoding=1252]),
Promote_Headers = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in
Promote_Headers
Thanks, Tamatam
No comments:
Post a Comment
Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.