Forum Discussion
DataSource.Error List.Accumulate
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
What's your reasoning for using List.Accumulate over Table.AddColumn ?
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.
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 !!
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,
Use Table.AddColumn , it is much simpler and faster.
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
CombinedDataPerhaps there's another approach to solving this problem. Any thoughts?
Thanks!
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.
