Okay...sometime Excel is so annoying...
here is how I fixed the problem:
As I supposed, the error was created by the query from the list of the legay query.
I can't transform the legay query into a table, because in this case Excel wants to remove the external connection from the list/table.
Now I got
Sheet1 = Legacy Query
Sheet2 = Formula: A1 = Sheet1!A1
Expanded this formula to A1:E50 because we haven't got so much entries in this live data.
With this range of A1:E50 I created a table and this table is used by the PQ, filtering only used rows of table.
Sheet3 = PQ result
Sheet1 + 2 are hidden and everything works fine... o_O
Many thanks for you assistance 🙂
Glad to hear you solved it.
To get data from a sheet in the current workbook, if you don't want to use a named range as suggested above, you have to use the file full path like this:
let
Source = Excel.Workbook(File.Contents("C:UsersCatalinDesktop1.xlsx"), null, true),
#"Filtered Rows" = Table.SelectRows(Source, each ([Kind] = "Sheet") and ([Item] = "Sheet1")),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Data"}),
#"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"Column1", "Column2"}, {"Column1", "Column2"})
in
#"Expanded Data"
You will then be able to select a specific sheet.
There is no way to get the current file full path in power query...
You still have to use a defined name in a cell with this formula:
=LEFT(CELL("filename",$A$1),FIND("[",CELL("filename",$A$1),1)-1)
Sheet1 = Legacy Query
Sheet2 = Formula: A1 = Sheet1!A1
Expanded this formula to A1:E50 because we haven't got so much entries in this live data.
With this range of A1:E50 I created a table and this table is used by the PQ, filtering only used rows of table.
Just from curiosity, why didn't you defined a named range for A1:E50 in Sheet1, where your query saves data?
You'll be able to use : Excel.CurrentWorkbook(){[Name="MyRange"]}[Content] to get that Sheet 1!A1:E50 range into power query, it does NOT have to be a table, as mentioned it can also be a NAMED RANGE... A named range is not the same thing as a table object.
Thank you very much!
The full path is not an option for me because I don't know, where the other members save their file.
Anyway thank you very very much!
😀
Excel.CurrentWorkbook() only returns defined names (only those referring to a range) and table objects, not sheets.If you want to get the range "A1:F100", give a name to that range, MyRange for example, then use:
Excel.CurrentWorkbook(){[Name="MyRange"]}[Content]
INGENIOUS!!!
Works!
