cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Resolver I
Resolver I

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

Hi,

I have defined my arranged data in excel worksheet as a table and have linked only the table (with given name) to the PBI file.

I have no issues even when I add columns in between existing columns. This may be because the table itself accepts the new column as its integral part and accordingly, the same is there to refer in PBI.

Hope this solves the issue

Helper II
Helper II

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

Hi san_jois,

 

Thanks for the prompt feedback when you say:

 

"I have defined my arranged data in excel worksheet as a table and have linked only the table (with given name) to the PBI file."

 

1. What steps do I need to perform to define an excel spreadsheet as a table and link it to the PBI file?

2. If I do this will it mean my data source essentially changes and so I will have to rebuild my dashboard?

3. Why doesnt the standard refresh work? Is this a known issue? When you refresh PBI appears to look at the source data file but only for changes in the data items that were originally imported, in my hands it doesnt pick up things like new columns added surely that cant be sensible?

 

If you can provide the additional info I will give your method a try

Highlighted
Resolver I
Resolver I

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

Hi,

 

Steps to define data arranged (meaning complete and formats defined) as a table:

i) Select a range in MS Excel, In Home, click 'format as table' with column headings (I assume you already must have given headings to all columns)

ii) On Design tab, give a name to this table

 

Steps to connect this table with Power BI:

i) Get data-Excel

ii) Browse your file and you will see the table appearing in the selection

iii) Select and thats it

 

You can add columns at the end or in the middle..automatically PBI updates it when you refresh the data

Also, whatever data gets added in rows stays updated in PBI upon refresh

 

Hope this answers your query..

Highlighted
Advocate I
Advocate I

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

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

Highlighted
New Member

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

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

Highlighted
New Member

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

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

Highlighted
Frequent Visitor

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

This worked for me even though the new columns where at the end of the excel file and it should have not been neccessary.

 

Thanks for the help.

 

Bruce

Highlighted
Helper II
Helper II

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

Here's an idea, if you are keeping the data source constant but modify the content in terms of adding or removing columns would it not be easier to allow functionality for the option to review and rewrite the "get data" query in Navigator rather than having to fiddle around with SQL text. I should add that I have done the SQL edit method myself so it works, some may say its quick and easy, but one of Power BI's strengths is to distance users from having to do a lot of sql query writing so personally I think something like the Navigator option should be available

Highlighted
New Member

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

Thank you YourBeerGuy- this did the trick for me!

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors