Change Data Source from offline excel to SharePoint

[FreyaNott]

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. 

[ahmedatef98]

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 · Accepted 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.

[ahmedatef98]

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]

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

[FreyaNott]

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?