Excel.Workbook function not working

[snandy2011]

Hi,

I am just importing multiple excel file from a folder.For that, I have wriiten a simple M function Excel.Workbook, but it does not work. It shows "The formula is incomplete".

See the below screenshot,

                                                  

My formula is so simple : =Excel.Workbook([Content],true).

I have written above same formula  into  excel's power query  and it works fine.It does not show me any error.But in power Bi'spower query it does not work.

I dont understand why it is not working. If the same formula works in excel, then why it is not working in power BI?

Can you please suggest me what wrong I have done?

Any suggesation is really appreciable.

Thanks,

snandy

[MFelix]

Hi @snandy2011,

Whe you used powerqueryit was in the file were you have the data correct?

In PBI part of the information is the location of the file so you need something like this

Excel.Workbook(File.Contents("C:\Projects\Examples\Customers and Orders.xlsx"), true),

Customers_Sheet = Source{[Item="Customers",Kind="Sheet"]}[Data]

So you need to define location of file.

But if you are making a query to get files from folder why don't you use the functionality from PBI it will create the code for you

https://docs.microsoft.com/en-us/power-bi/desktop-combine-binaries

If it's for learning purposes then you can analize the code created.

Regards,
MFelix

[snandy2011]

hi @MFelix ,

Thanks for replying.. i have used combine features before on PBI.but,my question was that, why the same code is not working on PBI,while it is working fine on Excel..yeah, on PBI, i have to do a little bit extra.that is, mentioning the location of the file.

anyway, Thanks for replying..

Regards,

snandy

[Stachu]

looking at the formula description in #shared it does have 3 arguments, but only 1st one is mandatory (binary), and on my side I couldn't replicate the issue when passing only 2 arguments. Could it be that one of the binaries is not Excel?

maybe try something like

Excel.Workbook([Content], true, null)

[MFelix · Accepted solution]

@snandy2011 ,

This as to do with the way you setup your function, if you do it from scratch you need to adjust the way of working.

When having a link from a folder if you notice the first step on the function is what you refer:

  Source = Excel.Workbook([content], null, true),

However in the combination of the files the first step is:

= Folder.Files("C:\Folder")

And you invoke the custom function from the C:\Folder.

So you are refering the folder path, however this depends on how you setup your function.

Regards,

MFelix