Forum Discussion

BMM27's avatar
BMM27
Helper I
2 years ago
Solved

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!


  • BMM27's avatar
    BMM27
    2 years ago

    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

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

    • BMM27's avatar
      BMM27
      Helper 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.

      • johnbasha33's avatar
        johnbasha33
        Super 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 !!