cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
FreyaNott
New Member

Change Data Source from offline excel to SharePoint

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. 

 

 

1 ACCEPTED SOLUTION
KNP
Super User
Super User

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 ;).
chrome-9xf-Zagzel-B

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.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

View solution in original post

5 REPLIES 5
ahmedatef98
Frequent Visitor

  1. Add a new source (make it through SharePoint and select the file you want)
  2. Go to Transform Data
  3. Select the new query (with SharePoint source) 
  4. Click Advanced Editor 
  5. Choose the first part of the code (as //Sharepoint source file code)
  6. Copy the code 
  7. Select the old query with the local source file 
  8. Click Advanced Editor
  9. Past the code till the end of the line starts with (#"Changed Type" 😃

 

//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

  • Change the variable name in each line in the copied code (you will find double characters in the provided code)
  • Make sure to change this variable name in the following line that it was used it 
  • no need to change the last name ((#"Changed Type")) as it is the same as the previous one

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 

 

KNP
Super User
Super User

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 ;).
chrome-9xf-Zagzel-B

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.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

I just would add 2 small comments, 

  1. The folder path needs to match the one when I make the filter.
    As I have tried it many times in different ways and it doesn't work (every time there was something different), So I copied the folder I need from the Filtered Rows step to make sure they are matched. 
  2. The names of the added lines, it is preferred to be changed to avoid any variable names' duplicates in the rest of your query (I made the following modifications to avoid errors and to avoid rewriting anything else except just copying and pasting for the too many queries
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. 

Greg_Deckler
Super User
Super User

@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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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?

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors