# Power Query - load from Excel file with variable sheet name



## ondas

I have a Power Query set up to load data from local Excel file (.xlsx). The file is exported from ERP and saved under identical filename in the same folder. It contains one sheet with transactional data (with ever increasing number of rows). Problem is that name of the sheet is randomly generated - string beginning with "E" and a sequence of numbers.

If I open the exported file and rename the sheet to "data", it works with this code generated automatically by Power Query:



		Code:
__


let
    Source = Excel.Workbook(File.Contents("\\cz11-r510fsvt\HonSa\VÝDEJKY\VYDEJKY CZ 2015.xlsx"), null, true),
    data_Sheet = Source{[Item="data",Kind="Sheet"]}[Data],


Is there a way to make this load definition dynamic, i.e. to import the sheet no matter what its name is?


----------



## ImkeF

In the data column: Instead of clicking on the table, expand the arrows in the header field.

This should read like: = Table.ExpandTableColumn(Source, "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7"}, 

So if your headers will stay the same and just rows being added, that's it.


----------



## ondas

Thank you very much, it works! Only drawback is that it requires to disable and reenable the query (as I am modifying an existing one), basically forcing me to start over, right? I google workaround for Excel 2010, but I am using Excel 2013 and I wasn't able to find a solution for my version


----------



## ImkeF

No! No need to redo the query. You just replace this line:
data_Sheet = Source{[Item="data",Kind="Sheet"]}[Data],

with this line: 
data_Sheet = Table.ExpandTableColumn(Source, "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7"}, 

Of course Column1... with your columns.

Just duplicate your current query (for safety reasons), change to these new lines and copy your new start into your old query!!


----------



## esadovnic

Also, ondas, in case you're worried about placing measures into the table...

One of the things I do is set up a Blank query and name it "A". I park all of my measures there which makes it easy to load/re-load all of your queries if you have to instead of losing all of your progress on the model/workbook. Calc columns are a different story, but at least this saves your measures in a relatively safe environment.


----------



## ondas

ImkeF said:


> You just replace this line:
> data_Sheet = Source{[Item="data",Kind="Sheet"]}[Data],
> 
> with this line:
> data_Sheet = Table.ExpandTableColumn(Source, "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7"},
> 
> Of course Column1... with your columns.



I tried it, but it still requires disabling and re-enabling  

Tried with two variants:


		Code:
__


data_Sheet = Table.ExpandTableColumn(Source, "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36", "Column37", "Column38"}),




		Code:
__


   data_Sheet = Table.ExpandTableColumn(Source, "Data", { "sk_hlavicka_cislo_subjektu", "x___cislo_objektu___x", "Číslo subjektu", "HL_Výdejka - reference", "HL_Datum realizace", "HL_poznámka", "HL_Zakázka - reference", "HL_Zakázka - název", "HL_Druh skl.pohybu - reference", "HL_Sklad - reference", "HL_Zavedl - název", "HL_Organizace - reference", "HL_Organizace - název", "HL_Org_Skupina organizací - název DV10837", "HL_Org_Skupiny organizace CTS DV8496", "HL_Org_Skupiny organizace CUSTOM. DV8496", "HL_Org - Země", "HL_Org_Region - reference DV8347", "HL_Org_Expozitura - reference", "HL_Org_Zodp.osoba DV10701", "HL_Fakturováno", "POL_KKZ - reference", "POL_KKZ - název", "POL_počet", "POL_Cena základ celk. položka", "POL_Cena evid.celk. položka", "POL_Kkz_Útvar", "POL_Kkz_Útvar_Produktové řady", "POL_Uda_Kkz_LOB", "POL_Kkz_Hierarch.skupina", "POL_Kkz_Skupina zboží", "POL_Kkz_aktuální dodavatel - reference", "POL_Kkz_aktuální dodavatel - název", "POL_Kkz_CZaktuální dodavatel - reference", "POL_Kkz_CZaktuální dodavatel - název", "POL_Kkz_Skupiny organizace SUPPLIER cz-sk", "HL_Org_Skupiny organizace B-E DV8496", "HL_Org_Skupiny organizace B-E DV8496"}),


Neither of them works...


----------



## ImkeF

Hm, problem is that I might not got what you meant with "requires disabline and re-enabling" - doing the query all over.

So if you do the following:

1) Start with a query that works
2) Duplicate this query and do the following steps on this query
3) In the Query Settings Pane in Applied Steps go to the first step: "Source"
4) click the arrow in the "data"column. 
5) You will end up with a query that doesn't work, because the 3rd step doesn't make any sense any more (your "old" data_Sheet -step): Delete this step
6) At least with my example, you would just have sucessfully converted your query to the new technique (actually no need to copy any code here). 

Or have a look at the Video:


----------



## ondas

I was able to duplicate my original query, change it according to your instructions and I can update it with new data. But how do I load the same (i.e. new) query instead of the original one? Right now I have two almost identical queries (in terms of data), but if I change definition of the first one according to the second one, I get the following error:

We couldn't refresh the table 'DATA - TRANSACTIONS CZ' from connection 'Query - data'. Here's the error message we got:
OLE DB or ODBC error: The query 'data' or one of its inputs was modified in Power Query after this connection was added. Please disable and re-enable loading to the Data Model for this query.

What am I missing?


----------



## ImkeF

Nothing - you just met the PQ-PP-bug 

Solutions see here for example: Solved: Power Query Issues with Power Pivot - Exception from HRESULT: 0x80020009


----------



## ondas

Hm, this means I am screwed  In any case thanks a lot for the link with explanation, at least I know what to avoid in future .

Is it able to (possibly using VBA) to list all the data connections from Power Query, indicating which are read-only? You know - to have a quick check if all the connections are ok, or if I messed up again during redesing


----------



## ondas

I have a Power Query set up to load data from local Excel file (.xlsx). The file is exported from ERP and saved under identical filename in the same folder. It contains one sheet with transactional data (with ever increasing number of rows). Problem is that name of the sheet is randomly generated - string beginning with "E" and a sequence of numbers.

If I open the exported file and rename the sheet to "data", it works with this code generated automatically by Power Query:



		Code:
__


let
    Source = Excel.Workbook(File.Contents("\\cz11-r510fsvt\HonSa\VÝDEJKY\VYDEJKY CZ 2015.xlsx"), null, true),
    data_Sheet = Source{[Item="data",Kind="Sheet"]}[Data],


Is there a way to make this load definition dynamic, i.e. to import the sheet no matter what its name is?


----------



## ImkeF

Sorry, I'm not aware of such a solution - but this means nothing, as I'm no VBA person.
Are you running the most recent PQ Version (2.26.4128.242)? 
At least with my example it behaves - so I acutally cannot reproduce the error any more. Astouning...


----------



## ondas

Actually no, I am running version 2.25.4095.242. In what aspect should it behave differently? I will end up with two queries and I will not be able to replace the original one with the duplicated (corrected) one anyway, right? You are not able to modify the original query with your newer version, are you?

Just asking because it's kinda tedious process in our company to have IT installed anything


----------



## ImkeF

Asked around and it looks like MS moved one step further in removing this bug with version 26. But this doesn't seem to be stable and also not working for all Excel-Versions. So this might not help you here, especially if you don't have an old version of your file with intact connections.

Sorry, for not being able to help you further here.


----------



## ondas

Nevermind, at least I have learned more about PowerPivot and Power Query 

Thank you so much!


----------



## pumilamac

I have a similar issue as ondas, but I wasn't able to follow the steps and have it work successfully. I tried a few different ways and get errors about syntax. I'm currently using Power BI Desktop July 2017 64 bit. I also searched at Home - Microsoft Power BI Community but could not find anything that matched this.

 My spreadsheet has only one sheet, the sheet name changes each day with a new date and a new 3 digit code. In Power BI I opened the Query Editor, and then Advanced Editor and what I see is below. Am I in the wrong area to make the changes, any help is appreciated.

let
    Source = Excel.Workbook(File.Contents("Z:\ERP\Reports\Daily3.xlsx"), null, true),
    #"08-04-2017+004_Sheet" = Source{[Item="08-04-2017+004",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(#"08-04-2017+004_Sheet",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}})
 in
    #"Changed Type"​


----------



## ImkeF

if its just one sheet in the file, its pretty easy to grab it: Just use {0}[Data]
This will grab the content from columnn [Data]s first row 

let
Source = Excel.Workbook(File.Contents("Z:\ERP\Reports\Daily3.xlsx"), null, true),
*MyData *= Source*{0}[Data]*,
#"Changed Type" = Table.TransformColumnTypes(*MyData*,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}})
in
#"Changed Type"


----------

