Append Tables from multiple excel files with the same name from Share Point Folder

[Anonymous]

Hello Team,

Could you help me please to understand how can I import multiple excel files from SharePoint folder, that has multiple subfolders with the exact same file name in each, into one Power BI report? The idea is to append all the tables into one master table.

Folders will be kept creating in the future. All the excel files have the same amount of columns with the same header names.

Thank you very much

[Anonymous]

@serpiva64 
DataSource.Error: Web.Contents failed to get contents from "my address"/_api/web/lists/getbyid('c2d87b8f-2b61-42b5-a218-9c2cdb4245a0')/RenderListDataAsStream?cid=2d887630-98c4-459d-8580-97d60f08d3ce' (405): Method Not Allowed
Details:
DataSourceKind=SharePoint
DataSourcePath="my address"/_api/web/lists/getbyid('c2d87b8f-2b61-42b5-a218-9c2cdb4245a0')/RenderListDataAsStream?cid=2d887630-98c4-459d-8580-97d60f08d3ce

Could you please also tell me where should I put the file names, please?

[serpiva64]

Hi,

Your adress should be like this:

https://_______________/sites/Ambienteditest/Documenti%20condivisi/Forms/AllItems.aspx

You need only the first part:

You obtain something like this:

And here, if you have in the same folder other files, you choose the one you need
then

and so on.

[Anonymous]

@serpiva64 
I created the Custom column but it gave me an error:

[serpiva64]

is it your M like this?

= Table.AddColumn(#"Filtered Rows", "Custom", each Excel.Workbook([Content]))

[Anonymous]

@serpiva64 
Okay, It did work! 
The only problem that the file has a lot of tables and it added all of them into 1 table. But it shouldn't be so because I need only 1. Is it possible to select only 1 table from the list? Thank you.

[serpiva64]

when you expand custom mantain also name

now you can filter the sheet you want

[Anonymous]

@serpiva64 

Works good. The only thing that It found 81 file at SharePoint folder and it gave me 81 rows per each ID as a duplicate. I can use "Delete Duplicates" in Power Query but it seems like not a good approach

[serpiva64]

i can't figure what you are saying. Can't send a screenshot?

[Anonymous]

@serpiva64 
So, I connected to the source and found 86 .xls files in the Share Point Folder.

I did exactly everything according to the flow you proposed.
In the end, I got each row duplicated 86 times

The flow is the next
let
Source = SharePoint.Files("https://my folder/", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "my file.xlsx")),
#"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
#"Added Custom" = Table.AddColumn(#"Filtered Hidden Files1", "Custom", each Table.AddColumn(#"Filtered Rows", "Custom", each Excel.Workbook([Content]))),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Custom"}, {"Custom.Custom"}),
#"Expanded Custom.Custom" = Table.ExpandTableColumn(#"Expanded Custom", "Custom.Custom", {"Name", "Data", "Item", "Kind"}, {"Name", "Data", "Item", "Kind"}),
#"Filtered Rows1" = Table.SelectRows(#"Expanded Custom.Custom", each ([Kind] = "Table")),
#"Filtered Rows3" = Table.SelectRows(#"Filtered Rows1", each [Name] = "Associates"),
#"Removed Other Columns1" = Table.SelectColumns(#"Filtered Rows3",{"Data"}),
#"Expanded Custom.Custom.Data" = Table.ExpandTableColumn(#"Removed Other Columns1", "Data", {"Column1", "Column2", "Column3", "Column4", "Column5" }),
#"Replaced Errors" = Table.ReplaceErrorValues(#"Expanded Custom.Custom.Data", {{"Column1", "Column2", "Column3", "Column4", "Column5" }})
in
#"Replaced Errors"

So as a solution I must use the "Remove duplicates" function but it looks like it is better to avoid it.

Thank you very much for the help

[serpiva64 · Accepted solution]

Ok,

i've seen the problem.

this is the wrong line

#"Added Custom" = Table.AddColumn(#"Filtered Hidden Files1", "Custom", each Table.AddColumn(#"Filtered Rows", "Custom", each Excel.Workbook([Content]))),

This is the rght one

#"Added Custom" = Table.AddColumn(#"Filtered Hidden Files1", "Custom", each Excel.Workbook([Content])),

you obtain it this way

[Anonymous]

@serpiva64 
Sorry, one more problem. Data keeps disappearing when I upload it in some fields.
When I work in Power Query I see that value distribution is 80% in the column "Status". It gives me all values that files have. But when I update the preview, filter preview, or load the data it just dissapears and gives me just some of the data or doesn't 

As a result, when I press "save and load" in the power query it returns only some random values but not the whole data that it should be.

[Anonymous]

Amazing! Thank you very much! Works great!

[serpiva64]

Hi,

try something like this

let
Source = SharePoint.Files("https://____________/sites/Ambienteditest/", [ApiVersion = 15]),
#"Added Custom" = Table.AddColumn(Source, "Custom", each Excel.Workbook([Content])),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Data"}, {"Custom.Data"}),
#"Expanded Custom.Data" = Table.ExpandTableColumn(#"Expanded Custom", "Custom.Data", {"Column1", "Column2"}, {"Column1", "Column2"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Expanded Custom.Data", [PromoteAllScalars=true])
in
#"Promoted Headers"

If this post is useful to help you to solve your issue consider giving the post a thumbs up 

 and accepting it as a solution !