Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi all. I am new to PowerBI and have been working in an offline Excel spreadsheet and PowerBI.
I want to upload the Excel and PowerBI to SharePoint so they can be worked on collaboratively. Is this possible? I have quite a lot of data manipulation within the PowerBI so would be useful for it not to just be connecting PowerBI to a new data source (if this makes sense) rather adjusting where the PowerBI is looking for the data.
Solved! Go to Solution.
If you go into 'Transform data' and look in the advanced editor for your query it will look something like this for local files...
// Local
let
Source = Excel.Workbook(File.Contents("D:\Downloads\book.xlsx"), null, true),
Sheet1_Sheet = Source{[Item = "Sheet1", Kind = "Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars = true]),
#"Changed Type" = Table.TransformColumnTypes(
#"Promoted Headers",
{{"a", Int64.Type}, {"b", Int64.Type}, {"c", Int64.Type}}
)
in
#"Changed Type"
What you need for SharePoint is something that looks like...
// SharePoint
let
Source = SharePoint.Files(
"https://youdomain.sharepoint.com/sites/yoursite",
[ApiVersion = 15]
),
#"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "book.xlsx")),
ExcelFile = #"Filtered Rows"
{
[
Name = "book.xlsx",
#"Folder Path"
= "https://yourdomain.sharepoint.com/sites/yoursite/Shared Documents/Data/"
]
}
[Content],
#"Imported Excel Workbook" = Excel.Workbook(ExcelFile),
Sheet1_Sheet = #"Imported Excel Workbook"{[Item = "Sheet1", Kind = "Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars = true]),
#"Changed Type" = Table.TransformColumnTypes(
#"Promoted Headers",
{{"a", Int64.Type}, {"b", Int64.Type}, {"c", Int64.Type}}
)
in
#"Changed Type"
Essentially, you need to replace the top row of the local one with the top couple of rows of the SharePoint one. Obviously that varies depending on the complexity of your setup.
Hope that helps.
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
//Local Source File Code Sample
let
Source = Excel.Workbook(File.Contents("C:\Users\user\Test\Post.xlsx"), null, false),
Sheet1_sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Removed Other Columns" = Table.SelectColumns(Sheet1_sheet, {"Column1", "Column2", "Column3", "Column4", "Column5"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Other Columns", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"TR", type text}, {"Focus", type text}})
//Sharepoint source file code sample
let
Source = SharePoint.Files("https://Companyname.sharepoint.com/sites/TeamGroup/", [ApiVersion = 15]),
#"Filtered Rowss" = Table.SelectRows(Source, each ([Name] = "Post.xlsx")),
#"Removed Columnss" = Table.RemoveColumns(#"Filtered Rowss",{"Name", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"}),
#"Filtered Hidden Files1" = Table.SelectRows(#"Removed Columnss", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Functionn1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (2)", each #"Transform File (2)"([Content])),
#"Removed Other Columnss1" = Table.SelectColumns(#"Invoke Custom Functionn1", {"Transform File (2)"}),
#"Expanded Table Columnn1" = Table.ExpandTableColumn(#"Removed Other Columnss1", "Transform File (2)", Table.ColumnNames(#"Transform File (2)"(#"Sample File (2)"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Columnn1",{{"TR", type text}, {"Focus", type text}})
Important Notes To avoid Errors
This step is very useful if you have made many operations to this table to avoid any variable names' duplicates
Please make sure you copy till this line ((#"Changed Type")) and same when you paste it
If you go into 'Transform data' and look in the advanced editor for your query it will look something like this for local files...
// Local
let
Source = Excel.Workbook(File.Contents("D:\Downloads\book.xlsx"), null, true),
Sheet1_Sheet = Source{[Item = "Sheet1", Kind = "Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars = true]),
#"Changed Type" = Table.TransformColumnTypes(
#"Promoted Headers",
{{"a", Int64.Type}, {"b", Int64.Type}, {"c", Int64.Type}}
)
in
#"Changed Type"
What you need for SharePoint is something that looks like...
// SharePoint
let
Source = SharePoint.Files(
"https://youdomain.sharepoint.com/sites/yoursite",
[ApiVersion = 15]
),
#"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "book.xlsx")),
ExcelFile = #"Filtered Rows"
{
[
Name = "book.xlsx",
#"Folder Path"
= "https://yourdomain.sharepoint.com/sites/yoursite/Shared Documents/Data/"
]
}
[Content],
#"Imported Excel Workbook" = Excel.Workbook(ExcelFile),
Sheet1_Sheet = #"Imported Excel Workbook"{[Item = "Sheet1", Kind = "Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars = true]),
#"Changed Type" = Table.TransformColumnTypes(
#"Promoted Headers",
{{"a", Int64.Type}, {"b", Int64.Type}, {"c", Int64.Type}}
)
in
#"Changed Type"
Essentially, you need to replace the top row of the local one with the top couple of rows of the SharePoint one. Obviously that varies depending on the complexity of your setup.
Hope that helps.
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
I just would add 2 small comments,
SharePoint_Source = SharePoint.Files(
"https://youdomain.sharepoint.com/sites/yoursite",
[ApiVersion = 15]
),
#"Filtered_Rows_0" = Table.SelectRows(SharePoint_Source, each ([Name] = "book.xlsx")),
ExcelFile = #"Filtered_Rows_0"
{
[
Name = "book.xlsx",
#"Folder Path"
= "https://yourdomain.sharepoint.com/sites/yoursite/Shared Documents/Data/"
]
}
[Content],
Source = Excel.Workbook(ExcelFile),
Sheet1_Sheet = Source{[Item = "Sheet1", Kind = "Sheet"]}[Data],
Many Thanks for the solution.
@FreyaNott So, typically the way that you do this is to create a new query that just connects to your new data source. Open this query in Advanced Editor and copy the Source and possibly Navigation lines at the top of the query. Now open your old query in Advanced Editor and replace the same lines with the copied lines.
Hi Greg,
Thank you for coming back to me! Apologies, as a new user you may need to break this down for me a little more. How would I connect the SharePoint file to the PowerBI?
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
24 | |
12 | |
12 | |
11 | |
7 |
User | Count |
---|---|
42 | |
27 | |
14 | |
13 | |
13 |