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 = /* Please specify your exact file path like below */
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
Notes:
We can set the Columns property to null as Columns=null instead of specifying the number of columns, to load all.
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
Notes:
We can set the Columns property to null as Columns=null instead of specifying the number of columns, to load all.
Thanks, Tamatam
No comments:
Post a Comment
Hi User, Thank You for visiting My Blog. If you wish, please share your genuine Feedback or comments only related to this Blog Posts. It is my humble request that, please do not post any Spam comments or Advertising kind of comments, which will be Ignored.