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.

     

KNP's avatar
KNP
Super User
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

    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.