Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 th...
Need Help
  • KNP's avatar
    KNP
    4 years ago

    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's avatar
ahmedatef98
Frequent Visitor
3 years ago
  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