Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
@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?
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.
is it your M like this?
= Table.AddColumn(#"Filtered Rows", "Custom", each Excel.Workbook([Content]))
@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.
when you expand custom mantain also name
now you can filter the sheet you want
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
i can't figure what you are saying. Can't send a screenshot?
@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
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
@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.
Amazing! Thank you very much! Works great!
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 !
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |