Forum Discussion
DataSource.Error List.Accumulate
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!
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
8 Replies
- lbendlinSuper User
What's your reasoning for using List.Accumulate over Table.AddColumn ?
- BMM27Helper I
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.
- johnbasha33Super User
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 !!