Forum Discussion

snandy2011's avatar
snandy2011
Helper IV
7 years ago
Solved

Excel.Workbook function not working

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

Need Help
  • MFelix's avatar
    MFelix
    7 years ago

    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

4 Replies

  • MFelix's avatar
    MFelix
    Super User
    7 years ago
    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's avatar
      snandy2011
      Helper IV
      7 years ago

      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

      • MFelix's avatar
        MFelix
        Super User
        7 years ago

        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