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.
Power BI Experts,
I am running into a challenge here with a report. I am needing to connect to different folders (all on SharePoint) for our organization. I don't want to usea data gateway at this point. I tried going down the path of "Sharepoint.Content" that i found in this blog:
https://radacad.com/power-bi-get-data-from-multiple-files-in-a-folder-on-onedrive-for-business-no-ga...
Unfortunately because I am having to connect to 10 different folders I am getting time out errors. Is there an effecient work around for this? Here is my query for one of them.
I also have the code here if you want to copy and paste it:
let
Query1 = SharePoint.Contents("https://xxxxxxxxx.sharepoint.com/XXXXXXX"),
Documents = Query1{[Name="Documents"]}[Content],
Administration = Documents{[Name="Administration"]}[Content],
PowerBI = Administration{[Name="PowerBI"]}[Content],
Procurement = PowerBI{[Name="Procurement"]}[Content],
#"PBA Visualization Tool" = Procurement{[Name="PBA Visualization Tool"]}[Content],
Projects = #"PBA Visualization Tool"{[Name="Projects"]}[Content],
#"Removed Other Columns" = Table.SelectColumns(Projects,{"Content", "Name"}),
#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each Excel.Workbook([Content])),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Custom.Name", "Custom.Data", "Custom.Item", "Custom.Kind", "Custom.Hidden"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each ([Custom.Kind] = "Sheet")),
#"Removed Other Columns1" = Table.SelectColumns(#"Filtered Rows",{"Name", "Custom.Data", "Custom.Item"}),
#"Expanded Custom.Data" = Table.ExpandTableColumn(#"Removed Other Columns1", "Custom.Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34"}, {"Custom.Data.Column1", "Custom.Data.Column2", "Custom.Data.Column3", "Custom.Data.Column4", "Custom.Data.Column5", "Custom.Data.Column6", "Custom.Data.Column7", "Custom.Data.Column8", "Custom.Data.Column9", "Custom.Data.Column10", "Custom.Data.Column11", "Custom.Data.Column12", "Custom.Data.Column13", "Custom.Data.Column14", "Custom.Data.Column15", "Custom.Data.Column16", "Custom.Data.Column17", "Custom.Data.Column18", "Custom.Data.Column19", "Custom.Data.Column20", "Custom.Data.Column21", "Custom.Data.Column22", "Custom.Data.Column23", "Custom.Data.Column24", "Custom.Data.Column25", "Custom.Data.Column26", "Custom.Data.Column27", "Custom.Data.Column28", "Custom.Data.Column29", "Custom.Data.Column30", "Custom.Data.Column31", "Custom.Data.Column32", "Custom.Data.Column33", "Custom.Data.Column34"}),
Thanks for your help!
@lbendlin - thanks for the reply. So would you recommend layering in the binary buffer code to each sub folder drill in step or just the one that you showed above?
only in the step where you actually pull content.
@lbendlin - thanks for the response. I tried that but clearly the issue is between the seat and the keyboard (on my end). I ended up throwing in the towel and setting up a personal data gateway on a VM to manage the refresh and that seems to be working as needed. I will continue to look into the Function.InvokeAfter() function and maybe will be able to get it figured out down the road.
Try binary buffers.
For example
#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each try Excel.Workbook([Content]) otherwise Binary.Buffer(Excel.Workbook([Content]))),
Also, learn about Function.InvokeAfter()
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 |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |