Forum Discussion

chur06's avatar
chur06
New Member
6 years ago
Solved

Multiple worksheets in sharepoint hosted Excel

Hi all

 

I'm wondering what the best practice is for importing multiple worksheets from one excel file hosted in sharepoint. 

 

I'm not having any issue importing one worksheet at a time from the same file but I'm wondering if there is a way to do it in bulk. I ask because I'm mildly concerned that for each worksheet I have to "get data" by pointing to the sharepoint folder, traversing the folder tree, then select the worksheet I want and basically repeat this process till all worksheets are imported. I assume there is a more efficent way than what I'm doing. 

 

TIA

Chris

Need Help
  • v-diye-msft's avatar
    v-diye-msft
    6 years ago

    Hi chur06 

     

    I would solve it by adding a step in the 'Transform Sample File' code to deal with the first and second sheets independently.

     

    Transform Sample File code:

     

    let
    Source = Excel.Workbook(Parameter1, null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    FirstSheet = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    Sheet2_Sheet = Source{[Item="Sheet2",Kind="Sheet"]}[Data],
    SecondSheet = Table.PromoteHeaders(Sheet2_Sheet, [PromoteAllScalars=true]),
    Custom1 = Table.Combine({FirstSheet, SecondSheet})

in
    Custom1

     

     

    Note that 'Sheet1_Sheet' references 'Source' and 'Sheet2_Sheet' also references 'Source' (not the previous step). 

    Then I just used 'Table.Combine' to join the two results together.

     

    Here is the code from the main query:

     

    let
    Source = Folder.Files("C:\Temp"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File")))
in
    #"Expanded Table Column1"

     

     

5 Replies

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    You can create all the queries for each sheet in one step, but they will still each be a separate query (just click on each sheet you want in the window that pops up when you first connect to the Excel workbook).  You can see all the tables/sheet/etc. in the workbook in the query editor too.  Just duplicate your query, and then right click on the Navigation step and choose delete until end.  You'll see all the content in that Excel workbook.  If your workbook has identical table/sheets (i.e., same columns different data), you can then expand the tables or apply a function to get the sheets in one query.

     

    However, it is common to have separate queries for sheets/tables in the same workbook, so don't worry about it.  If you really did want to avoid that you could have a query that gets the workbook contents (no Navigate step) and then "reference" that base query to get each table/worksheet.  If so, you would also want to uncheck a couple fields in the options (Allow Background refresh and Enable parallel loading), but again you are probably fine with separate queries.

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

    • RebelFlamingo's avatar
      RebelFlamingo
      New Member
      2 years ago
      Is one way better than the other, or are both acceptable methods in real world data?
  • v-diye-msft's avatar
    v-diye-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi chur06 

     

    I would solve it by adding a step in the 'Transform Sample File' code to deal with the first and second sheets independently.

     

    Transform Sample File code:

     

    let
    Source = Excel.Workbook(Parameter1, null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    FirstSheet = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    Sheet2_Sheet = Source{[Item="Sheet2",Kind="Sheet"]}[Data],
    SecondSheet = Table.PromoteHeaders(Sheet2_Sheet, [PromoteAllScalars=true]),
    Custom1 = Table.Combine({FirstSheet, SecondSheet})

in
    Custom1

     

     

    Note that 'Sheet1_Sheet' references 'Source' and 'Sheet2_Sheet' also references 'Source' (not the previous step). 

    Then I just used 'Table.Combine' to join the two results together.

     

    Here is the code from the main query:

     

    let
    Source = Folder.Files("C:\Temp"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File")))
in
    #"Expanded Table Column1"

     

     

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    6 years ago

    the real question here is if these worksheets all have the same structure and if you want to combine them into one query.

     

    If the answer is no then you need to repeat all the steps (with a potential shortcut of duplicating queries or parts of queries, either by right clicking on the query or by copy/paste of the M code).

     

    Personally I would not use Excel files unless absolutely necessary. CSV files are much faster to ingest.