Forum Discussion

enablejulian's avatar
enablejulian
Frequent Visitor
6 years ago
Solved

Error message - loading data from folder

Hi   I am trying to create a new dataset with data from excel documents saved on my local computer. All three excels are in the same folder are all the same format .xlsx and have the same headings,...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi enablejulian ,

     

    From what I can see apart from being a different name, they also have a slightly different structure.

    This is the step where you most likely have this error (you should have something similar in the template created by PBI):

    Table1_Table = Source{[Item="Sheet1",Kind="Sheet"]}[Data]

    This works Ok, so I changed it to:

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

    This is the error that I get:

    Expression.Error: The key didn't match any rows in the table.
Details:
    Key=
        Item=Sheet12
        Kind=Sheet
    Table=[Table]

    Looks familiar? 😁

     

    Somewhere in the Query list, you should have something like this. This is the bits that are created by PBI automatically when you do folder import:

     

    On the Sample File try changing the step that is usually called Navigation  and looks like this:

    Roll the number in {} from 0 to 2 and check what is happening with the Transform Sample File table, which is the template for the function that PBI generates to import files. If it brakes, you know will find causes it. 

     

    If you have to have different names for tabs in different files (e.g. for csv it is usually defaulting to the file name), let me know there is a way to tweak the code to adapt for it.

     

    Kind regards,

    JB

enablejulian's avatar
enablejulian
Frequent Visitor
6 years ago

Hi Anonymous ,

 

The name dim_items_Aug is actually the name of one of the files. The other files are named dim_items_july and dim_items_sept.

 

I assume that the purpose of importing from folder is that I can import excels that have identical structure, the only difference being the file name (?)

 

Thanks again

 

Julian

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi enablejulian ,

 

From what I can see apart from being a different name, they also have a slightly different structure.

This is the step where you most likely have this error (you should have something similar in the template created by PBI):

Table1_Table = Source{[Item="Sheet1",Kind="Sheet"]}[Data]

This works Ok, so I changed it to:

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

This is the error that I get:

Expression.Error: The key didn't match any rows in the table.
Details:
    Key=
        Item=Sheet12
        Kind=Sheet
    Table=[Table]

Looks familiar? 😁

 

Somewhere in the Query list, you should have something like this. This is the bits that are created by PBI automatically when you do folder import:

 

On the Sample File try changing the step that is usually called Navigation  and looks like this:

Roll the number in {} from 0 to 2 and check what is happening with the Transform Sample File table, which is the template for the function that PBI generates to import files. If it brakes, you know will find causes it. 

 

If you have to have different names for tabs in different files (e.g. for csv it is usually defaulting to the file name), let me know there is a way to tweak the code to adapt for it.

 

Kind regards,

JB

  • enablejulian's avatar
    enablejulian
    Frequent Visitor
    6 years ago

    Hi Anonymous 

     

    Thankyou! I think I have resolved.

     

    I followed your instructions and realised that whilst the data is consistent in each of my excel files, the name of the sheet (tab) is different. Aug file has sheet named dim_items_aug and July has sheet named dim_items_july etc etc

     

    I've changed each of the sheets to be the same name (e.g. dim items) and I am now able to load 🙂

     

    Thanks for your assistance - I knew it would be something so simple!

     

    Thanks again

     

    Julian