cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ewuchatka Regular Visitor
Regular Visitor

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 it I can see is to build it again from scratch. However, there must be a way round it!

 

Please help Smiley Happy

 

Thank you

 

Ewa

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Add a new column to the existing data set and refresh

@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.

Near SE WI? Join our PUG MSBIWI
17 REPLIES 17
Super User
Super User

Re: Add a new column to the existing data set and refresh

@ewuchatka This should just work... Add the column to the Excel file, save, click refresh in Power BI. The new column should appear.

If you modify a column, then that is a different process.

Near SE WI? Join our PUG MSBIWI
PavelR Established Member
Established Member

Re: Add a new column to the existing data set and refresh

@ewuchatka

add your column to your excel sheet and refresh.

 

Regards.

Pavel

ewuchatka Regular Visitor
Regular Visitor

Re: Add a new column to the existing data set and refresh

Hi both @PavelR @Seth_C_Bauer

 

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

 

 

 

 

 

 

Super User
Super User

Re: Add a new column to the existing data set and refresh

@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.

Near SE WI? Join our PUG MSBIWI
PavelR Established Member
Established Member

Re: Add a new column to the existing data set and refresh

@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 Regular Visitor
Regular Visitor

Re: Add a new column to the existing data set and refresh

Hey both, I will try your solution today or tomorrow Smiley Happy

 

Thanks very much

 

Ewa

v-huizhn-msft Super Contributor
Super Contributor

Re: Add a new column to the existing data set and refresh

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.

2.PNG3.png4.PNG

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

ewuchatka Regular Visitor
Regular Visitor

Re: Add a new column to the existing data set and refresh

Thanks all very much for your help. I think I know how to do it now.... I'm testing it Smiley Happy

hobe1 Regular Visitor
Regular Visitor

Re: Add a new column to the existing data set and refresh

Hi All,

 

I am having the same problem with a power BI dashboard and the solution mentioned in this thread do not appear to fix the issue, I am using the September 2017 update of Power BI Professional.

 

1. My existing dashboard works just fine

2. Added a new column TO THE END (not inserted in the middle) of the existing excel spreadsheet source and refreshed

3. Did a refresh no notification of any errors with the refresh

4. New Column does not appear in the DATA view of Power BI and new data item does not appear in the FIELDS Listing to the right

5. I tried editing the query just in case it had updated the new data column but was not displaying it but I get a "Data not found" error when I do this.

 

So to summarise adding a new column of populated data to the end of a dashboard source excel spreadsheet does not appear to be imported into the Power BI database on refresh. Like the original poster I do not want to have to rebuild the entire dashboard

 

HELP! Smiley Happy