DataSource.Error List.Accumulate

[BMM27]

Hi, I want to take data from different .xlsx on SharePoint with distinct variables.
And on the LoadData step it brokes with this error: DataSource.Error: SharePoint: Request failed: The remote server returned an error: (503) Server Unavailable. (Service Unavailable)
Details:
DataSourceKind=SharePoint
DataSourcePath=http://xxx.xlsx/  
Url=http://xxx.xlsx/_vti_bin/ListData.svc  
Here is the code:

let
Source = SharePoint.Files("https://xxxxxx.sharepoint.com/xxxxx/xxxxxx  ", [ApiVersion = 15]),

FilteredFiles = Table.SelectRows(Source, each Text.StartsWith([Name], "xxx") or Text.StartsWith([Name], "xxx")),

LoadData = List.Accumulate(FilteredFiles[Name], null, (state, current) =>
let
ExcelContent = SharePoint.Files(current){0}[Content],

ExcelWorkbook = Excel.Workbook(ExcelContent),

DataSheet =
if Text.StartsWith(current, "xx") then
ExcelWorkbook{[Item="xxx", Kind="Sheet"]}[Data]
else if Text.StartsWith(current, "xx") then
ExcelWorkbook{[Item="xxx", Kind="Sheet"]}[Data]
else
null,

SkipFirstRow = if state = null then DataSheet else Table.Skip(DataSheet, 1),

AddSourceColumn = Table.AddColumn(SkipFirstRow, "FileSource", each current, type text)
in
if state = null then AddSourceColumn else Table.Combine({state, AddSourceColumn})
),

CombinedData = Table.Combine(LoadData)
in
CombinedData

If anyone can help me I would appreciate, thanks!

[lbendlin]

What's your reasoning for using List.Accumulate over Table.AddColumn ?

[BMM27]

Hi, sorry for not responding earlier, I was out last week.

What I'm looking to do is combine data from various documents by stacking them on top of each other. Since they all have the same columns, I want to unify them automatically with power bi.

[lbendlin]

Use Table.AddColumn , it is much simpler and faster.

[BMM27]

Hi @lbendlin.
I tried somthing like this. But it stills doesn't work.

I have a SharePoint folder with different documents. First, I filter all the Excel files, which works fine. Now, I want to extract data from each document. The idea is to stack the data from each document one below the other. We have two types of Excel files: type1 with Sheet1 and type2 with Sheet2. Both types have the same structure, so we can stack them without any issues.

My last attempt:

let
FolderUrl = "https://xxx.sharepoint.xxx",

Source = SharePoint.Files("https://xxx.sharepoint.xxx", [ApiVersion = 15]),

FilteredFiles = Table.SelectRows(Source, each Text.StartsWith([Name], "xx") or Text.StartsWith([Name], "xx")),

LoadData = (file) =>
let
ExcelContent = SharePoint.Files(FolderUrl & file){0}[Content],

ExcelWorkbook = Excel.Workbook(ExcelContent),

DataSheet =
if Text.StartsWith(file, "x") then
ExcelWorkbook{[Item="xx", Kind="Sheet"]}[Data]
else if Text.StartsWith(file, "x") then
ExcelWorkbook{[Item="xx", Kind="Sheet"]}[Data]
else
null,

SkipFirstRow = if DataSheet <> null then Table.Skip(DataSheet, 1) else null,

AddSourceColumn = Table.AddColumn(SkipFirstRow, "FileSource", each file, type text)
in
AddSourceColumn,
CombinedData = Table.FromRecords(List.Transform(FilteredFiles[Name], each LoadData(_)))
in
CombinedData

Perhaps there's another approach to solving this problem. Any thoughts?

Thanks!

[lbendlin]

as I said, instead of ExcelContent = SharePoint.Files(FolderUrl & file){0}[Content] you should use Table.AddColumn .  There you can implement your sheet switching logic.   After that you only need to expand the custom column and discard all unwanted fields.

[BMM27 · Accepted solution]

I think I have it! 
For other people, this code:
Takes a sharepoint folder

• Filter excels by name
• Depending on the File name takes different sheets
• Finally combine all data on one table.

Here is the code:

let
FolderUrl = "sharepoint folder",

//Get the list of files from SharePoint
Source = SharePoint.Files("sharepoint", [ApiVersion = 15]),

//Filter the files to select only those starting with "A" or "B"
FilteredFiles = Table.SelectRows(Source, each Text.StartsWith([Name], "A") or Text.StartsWith([Name], "B")),

//Define a function to load data from each Excel file
LoadData = (file as text) =>
let
//Fetch the binary content of the Excel file
ExcelContent = Web.Contents(FolderUrl & file),

//Load the Excel workbook
ExcelWorkbook = Excel.Workbook(ExcelContent),

//Determine the sheet name based on the file name
DataSheetName =
if Text.StartsWith(file, "A") then "C"
else if Text.StartsWith(file, "B") then "D"
else null,

//Extract data from the specified sheet
DataSheet = if DataSheetName <> null then ExcelWorkbook{[Item=DataSheetName, Kind="Sheet"]}[Data] else null,

//Skip the first row if data exists
SkipFirstRow = if DataSheet <> null then Table.Skip(DataSheet, 1) else null,

//Add a column to identify the source file
AddSourceColumn = Table.AddColumn(SkipFirstRow, "FileSource", each file, type text)
in
//Return the resulting table
AddSourceColumn,

//Apply the LoadData function to each file and combine the results
CombinedData = Table.Combine(List.Transform(FilteredFiles[Name], each LoadData(_)))
in
CombinedData

[johnbasha33]

Hi @BMM27  

Power BI can indeed help you with that task. You can achieve this through the data modeling capabilities in Power BI. Here's a general approach you can take:

1. **Data Source Connection**: First, connect Power BI to each of the documents containing your data. You can connect to various data sources including Excel files, CSV files, databases, etc.

2. **Query Editor**: Once connected, you'll use the Query Editor in Power BI to transform and combine the data. Each document will be loaded as a separate query.

3. **Data Transformation**: In the Query Editor, you can perform any necessary data cleaning, transformations, or calculations on each dataset to ensure consistency and compatibility. Make sure the columns you want to stack are identical across all datasets.

4. **Append Queries**: After preparing the data from each document, you can append the queries together. In Power BI, this is done by selecting the queries you want to append and using the "Append Queries" feature.

5. **Data Model**: Once you have appended the queries together, you'll have a single consolidated dataset. You can then load this data into the Power BI data model for further analysis and visualization.

6. **Report Building**: Finally, you can create reports and visualizations using the unified dataset in Power BI Desktop.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

[BMM27]

Hi @johnbasha33 , but I have two conditions regarding the different documents. Firstly, if I add more documents to SharePoint, I want Power BI to automatically include them as new data sources. As you can see in the code, I'm using "startsWith" to fetch the data. Secondly, I have two types of documents, each requiring a different sheet name to be fetched. In summary, I want new data sources and data to be added automatically.

if Text.StartsWith(current, "xx") then
ExcelWorkbook{[Item="xxx", Kind="Sheet"]}[Data]
else if Text.StartsWith(current, "xx") then
ExcelWorkbook{[Item="xxx", Kind="Sheet"]}[Data]
else
null,