Forum Discussion

ewuchatka's avatar
ewuchatka
Icon for Helper II rankHelper II
9 years ago
Solved

Add a new column to the existing data set and refresh

Hello   I would like to add a new column to the existing data set in Excel and refresh my dashboard.   I have already built a dashboard based on the data set and at the moment the only way round ...
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    9 years ago

    ewuchatka It is most likely because you are inserting it into the middle... You can do one of two things.

    Easier - put it at the end of the Excel doc, and refresh

    Harder - keep it where it is. Go to Edit Queries -> highlight the query you are working with -> Advanced Editor and you will see the M code that is pulling the data. look at how the other columns are being added, and insert your new column in between the columns that are being pulled. Refresh in query editor, close & apply. And you should be good to go.

PavelR's avatar
PavelR
Icon for Solution Specialist rankSolution Specialist
9 years ago

ewuchatka

add your column to your excel sheet and refresh.

 

Regards.

Pavel

ewuchatka's avatar
ewuchatka
Icon for Helper II rankHelper II
9 years ago

Hi both PavelR Anonymous

 

It is not working, when I add a new column in the middle of the data set, I save it and refresh my Power BI I get an error:

 

"Expression.Error: The Column "Column 31" of the table wasn't found."

 

 

Any ideas?

 

Thanks

 

Ewa

 

Ewa

 

 

 

 

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    ewuchatka It is most likely because you are inserting it into the middle... You can do one of two things.

    Easier - put it at the end of the Excel doc, and refresh

    Harder - keep it where it is. Go to Edit Queries -> highlight the query you are working with -> Advanced Editor and you will see the M code that is pulling the data. look at how the other columns are being added, and insert your new column in between the columns that are being pulled. Refresh in query editor, close & apply. And you should be good to go.

    • YOURBEERGUY's avatar
      YOURBEERGUY
      New Member
      8 years ago

      I had the same challenges adding 2 columns to the end of a worksheet. I mirrorred the verbiage in the advanced editor but still got 'column not found' errors.  After some detective work i noticed that there is a 'column count' towards the top of the advanced editor.  When i changed that from 17 to 19 (+2 columns) and added the correct verbaige {"NEW ACCOUNT NAME", type text} or whatever it was, BINGO.  MUST CHANGE THE COLUMN COUNT IN ADVANCED EDITOR

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Thank you YourBeerGuy- this did the trick for me!

    • YOURBEERGUY's avatar
      YOURBEERGUY
      New Member
      8 years ago

      I had the same challenges adding 2 columns to the end of a worksheet. I mirrorred the verbiage in the advanced editor but still got 'column not found' errors.  After some detective work i noticed that there is a 'column count' towards the top of the advanced editor.  When i changed that from 17 to 19 (+2 columns) and added the correct verbaige {"NEW ACCOUNT NAME", type text} or whatever it was, BINGO.  MUST CHANGE THE COLUMN COUNT IN ADVANCED EDITOR

    • SA_NYC's avatar
      SA_NYC
      Icon for Advocate I rankAdvocate I
      8 years ago

      Ah terrific, thanks for that pointer about adding it to the end of the table, I didn't know that. Very helpful.

  • PavelR's avatar
    PavelR
    Icon for Solution Specialist rankSolution Specialist
    9 years ago

    ewuchatka

    It is because of the fact that you have done some steps in Query editor for this data source -> Advanced editor.

     

    Standardly it should work.

    • ewuchatka's avatar
      ewuchatka
      Icon for Helper II rankHelper II
      9 years ago

      Hey both, I will try your solution today or tomorrow :)

       

      Thanks very much

       

      Ewa

      • v-huizhn-msft's avatar
        v-huizhn-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        Hi ewuchatka,

        As I tested, it must work as follows. After add a column in excel, then click the refresh button highlighted in red line.



        Please check your Power Query as other customer posted. And please mark the corresponding reply which is helpful as solution, so more people can find solution easily.

         

        Best Regards,
        Angelia