Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Add an extra column from excel source

Hi,

 

I've tried som of the answer in here without succes.

 

In my Excel file, which is my data source, i've added an extra column. This extra colum I want to be a part of the dataset in BI. It doesn't help to press "Refresh". 

 

I've tried an answer in here, which was:

 

"1) refresh everything, make sure the new column is in the source csv

2) under applied steps in query editor, go to source and hit the settings gear icon to right of "source"

3) Choose Open file as "Automatic" hit OK

4) Your table will dissappear, double click the csv file icon, hit "insert" when the insert step box pops up

5) Your newly added column will appear at step 1 "source"

6) to make sure it stays , under "choose columns" or if you have a "removed columns" ...make sure the check box for that new column is checked

7) Your column is good to go now"

 

That didn't work either. I got this fail-message, when i applied the changes: Expression.Error: The key didn't match any rows in the table.

 

Is there anyone, who can help?

 

Regards Jonas

 

 

Need Help
  • MarcelBeug's avatar
    MarcelBeug
    8 years ago

    To be honest, I can't quite follow your steps, as these mention csv which is not applicable here.

     

    Looking at your query code, it seems to me that the issue is with:

     

    #"Power BI_Sheet" = #"Removed Other Columns"{[Item="Power BI",Kind="Sheet"]}[Data],

    as you have no "Power BI" sheet in the list of objects in your Excel workbook.

    Edit: this also corresponds with your error message.

     

     

    Shouldn't your code just be:

     

    #"Power BI_Sheet" = #"Removed Other Columns"{[Item="Power BI 2018",Kind="Sheet"]}[Data],

    Then you should see your additional colum.

     

    At #"Changed Type", you may want to adjust the column type of your new column, which is type any (ABC123) by default.

     

    Just some screen shot from another topic:

     

6 Replies

  • MarcelBeug's avatar
    MarcelBeug
    Community Champion
    8 years ago

    This is not an error that would appear in general, when adding a column to your Excel file.

     

    So it must be something specific we are not able to determine without more relevant information from your side.

    For example: query code, example data, at which step does the error occur (for the first tme).

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      New column = Kolonne

       

       

      The fail occur after I pres "Close and apply" in the query editor. I follow the written steps from the answer in another thread. 

       

      The tab i use for my visualization is Power BI 2018.

       

      Can the problem appear because i made calculated colums in BI where the new column would be?

      • MarcelBeug's avatar
        MarcelBeug
        Community Champion
        8 years ago

        Did you go through all query steps (just click each applied step one by one)? Most likely you would get the error at one of the steps already.

         

        Please go the Advanced Editor, select and copy the entire query code (you may take out any confidential information from the Source step), press "Insert code" in your reply here (see picture) and paste the copied code. OK.