cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ewuchatka
Helper II
Helper II

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 🙂

 

Thank you

 

Ewa

1 ACCEPTED SOLUTION

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


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

View solution in original post

24 REPLIES 24
hobe1
Helper II
Helper II

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! 🙂

 

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

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

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

I have a similar problem and while some of the solutions seem elegant which solves the issue right at the M code level, I am unfortunately still not able to find the number of columns in the advanced editor window :Column names - but cannot find the numberColumn names - but cannot find the number

PavelR
Solution Specialist
Solution Specialist

@ewuchatka

add your column to your excel sheet and refresh.

 

Regards.

Pavel

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

 

 

 

 

 

 

PavelR
Solution Specialist
Solution Specialist

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

Hey both, I will try your solution today or tomorrow 🙂

 

Thanks very much

 

Ewa

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

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

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


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

I've added few column in Advanced editor, but after that I get an error message: We expected newColumnNames to have the same number of items as columnNames. 

 

Source: 

= Table.ExpandTableColumn(#"Removed Columns1", "Data", {"Column1", "Column1100", "Column2", "Column3", "Column4", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column1610", "Column1611", "Column1612", "Column1613", "Column1614", "Column1615", "Column1616", "Column1617", "Column1618", "Column1619", "Column1620", "Column1621", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36", "Column37", "Column38", "Column39", "Column40", "Column41", "Column412", "Column42", "Column43", "Column44", "Column442", "Column443", "Column444", "Column445", "Column446", "Column447", "Column448", "Column449", "Column450", "Column451", "Column452", "Column453", "Column45", "Column46", "Column47", "Column48", "Column49", "Column50", "Column51", "Column52", "Column53", "Column54", "Column55", "Column56", "Column57", "Column58", "Column59", "Column60", "Column61", "Column62", "Column63", "Column64", "Column65", "Column66", "Column67", "Column68", "Column69", "Column692", "Column70", "Column71", "Column72", "Column722", "Column723", "Column724", "Column725", "Column726", "Column727", "Column728", "Column729", "Column730", "Column731", "Column732", "Column733", "Column73", "Column74", "Column75", "Column76", "Column77", "Column78", "Column79", "Column80", "Column81", "Column82", "Column83", "Column84", "Column85", "Column86", "Column87", "Column88", "Column89", "Column90", "Column91", "Column92", "Column93", "Column94", "Column95", "Column96", "Column97", "Column972", "Column98", "Column99", "Column100", "Column1002", "Column101", "Column102", "Column103", "Column1032", "Column1033", "Column1034", "Column1035", "Column1036", "Column1037", "Column1038", "Column1039", "Column1040", "Column1041", "Column1042", "Column1043", "Column104", "Column105", "Column106", "Column107", "Column108", "Column109", "Column110", "Column111", "Column112", "Column113", "Column114", "Column115", "Column116", "Column117", "Column118", "Column119", "Column120", "Column121", "Column122", "Column123", "Column124", "Column125", "Column126", "Column127", "Column128", "Column1282", "Column129", "Column130", "Column131", "Column1312", "Column1313", "Column1314", "Column1315", "Column1316", "Column1317", "Column1318", "Column1319", "Column1320", "Column1321", "Column1322", "Column1323", "Column132", "Column133", "Column134", "Column135", "Column136", "Column137", "Column138", "Column139", "Column140", "Column141", "Column142", "Column143", "Column144", "Column145", "Column146", "Column147", "Column148", "Column149", "Column150", "Column151", "Column152", "Column153", "Column154", "Column155", "Column156", "Column157", "Column158", "Column159", "Column1592", "Column160", "Column161", "Column162", "Column1622", "Column1623", "Column1624", "Column1625", "Column1626", "Column1627", "Column1628", "Column1629", "Column1630", "Column1631", "Column1632", "Column1633", "Column163", "Column164", "Column165", "Column166", "Column167", "Column168", "Column169", "Column170", "Column171", "Column172", "Column173", "Column174", "Column175", "Column176", "Column177", "Column178", "Column179", "Column180", "Column181", "Column182", "Column183", "Column184", "Column185", "Column186", "Column187", "Column1872", "Column188", "Column189", "Column190", "Column1902", "Column1903", "Column1904", "Column1905", "Column1906", "Column1907", "Column1908", "Column1909", "Column1910", "Column1911", "Column1912", "Column1913", "Column191", "Column192", "Column193", "Column194", "Column195", "Column196", "Column197", "Column198", "Column199", "Column200", "Column201", "Column202", "Column203", "Column204", "Column205", "Column206", "Column207", "Column208", "Column209", "Column210", "Column211", "Column212", "Column213", "Column214", "Column215", "Column2152", "Column216", "Column217", "Column218", "Column2182", "Column219", "Column220", "Column221", "Column2212", "Column2213", "Column2214", "Column2215", "Column2216", "Column2217", "Column2218", "Column2219", "Column2220", "Column2221", "Column2222", "Column2223", "Column222", "Column223", "Column224", "Column225", "Column226", "Column227", "Column228", "Column229", "Column230", "Column231", "Column232", "Column233", "Column234", "Column235", "Column236", "Column237", "Column238", "Column239", "Column240", "Column241", "Column242", "Column243", "Column244", "Column245", "Column246", "Column2462", "Column247", "Column248", "Column249", "Column2492", "Column2493", "Column2494", "Column2495", "Column2496", "Column2497", "Column2498", "Column2499", "Column2500", "Column2501", "Column2502", "Column2503", "Column250", "Column251", "Column252", "Column253", "Column254", "Column255", "Column256", "Column257", "Column258", "Column259", "Column260", "Column261", "Column262", "Column263", "Column264", "Column265", "Column266", "Column267", "Column268", "Column269", "Column270", "Column271", "Column272", "Column273", "Column274", "Column2742", "Column275", "Column276", "Column277", "Column2772", "Column278", "Column279"}, {"Data.Column1", "Data.Column1100", "Data.Column2", "Data.Column3", "Data.Column4", "Data.Column5", "Data.Column6", "Data.Column7", "Data.Column8", "Data.Column9", "Data.Column10", "Data.Column11", "Data.Column12", "Data.Column13", "Data.Column14", "Data.Column15", "Data.Column16", "Data.Column1610", "Data.Column1611", "Data.Column1612", "Data.Column1613", "Data.Column1614", "Data.Column1615", "Data.Column1616", "Data.Column1617", "Data.Column1618", "Data.Column1619", "Data.Column1620", "Data.Column1621", "Data.Column17", "Data.Column18", "Data.Column19", "Data.Column20", "Data.Column21", "Data.Column22", "Data.Column23", "Data.Column24", "Data.Column25", "Data.Column26", "Data.Column27", "Data.Column28", "Data.Column29", "Data.Column30", "Data.Column31", "Data.Column32", "Data.Column33", "Data.Column34", "Data.Column35", "Data.Column36", "Data.Column37", "Data.Column38", "Data.Column39", "Data.Column40", "Data.Column41", "Data.Column412", "Data.Column42", "Data.Column43", "Data.Column44", "Data.Column442", "Data.Column443", "Data.Column444", "Data.Column445", "Data.Column446", "Data.Column447", "Data.Column448", "Data.Column449", "Data.Column450", "Data.Column451", "Data.Column452", "Data.Column453", "Data.Column45", "Data.Column46", "Data.Column47", "Data.Column48", "Data.Column49", "Data.Column50", "Data.Column51", "Data.Column52", "Data.Column53", "Data.Column54", "Data.Column55", "Data.Column56", "Data.Column57", "Data.Column58", "Data.Column59", "Data.Column60", "Data.Column61", "Data.Column62", "Data.Column63", "Data.Column64", "Data.Column65", "Data.Column66", "Data.Column67", "Data.Column68", "Data.Column69", "Data.Column692", "Data.Column70", "Data.Column71", "Data.Column72", "Data.Column722", "Data.Column723", "Data.Column724", "Data.Column725", "Data.Column726", "Data.Column727", "Data.Column728", "Data.Column729", "Data.Column730", "Data.Column731", "Data.Column732", "Data.Column733", "Data.Column73", "Data.Column74", "Data.Column75", "Data.Column76", "Data.Column77", "Data.Column78", "Data.Column79", "Data.Column80", "Data.Column81", "Data.Column82", "Data.Column83", "Data.Column84", "Data.Column85", "Data.Column86", "Data.Column87", "Data.Column88", "Data.Column89", "Data.Column90", "Data.Column91", "Data.Column92", "Data.Column93", "Data.Column94", "Data.Column95", "Data.Column96", "Data.Column97", "Data.Column972", "Data.Column98", "Data.Column99", "Data.Column100", "Data.Column1002", "Data.Column101", "Data.Column102", "Data.Column103", "Data.Column1032", "Data.Column1033", "Data.Column1034", "Data.Column1035", "Data.Column1036", "Data.Column1037", "Data.Column1038", "Data.Column1039", "Data.Column1040", "Data.Column1041", "Data.Column1042", "Data.Column1043", "Data.Column104", "Data.Column105", "Data.Column106", "Data.Column107", "Data.Column108", "Data.Column109", "Data.Column110", "Data.Column111", "Data.Column112", "Data.Column113", "Data.Column114", "Data.Column115", "Data.Column116", "Data.Column117", "Data.Column118", "Data.Column119", "Data.Column120", "Data.Column121", "Data.Column122", "Data.Column123", "Data.Column124", "Data.Column125", "Data.Column126", "Data.Column127", "Data.Column128", "Data.Column1282", "Data.Column129", "Data.Column130", "Data.Column131", "Data.Column1312", "Data.Column1313", "Data.Column1314", "Data.Column1315", "Data.Column1316", "Data.Column1317", "Data.Column1318", "Data.Column1319", "Data.Column1320", "Data.Column1321", "Data.Column1322", "Data.Column1323", "Data.Column132", "Data.Column133", "Data.Column134", "Data.Column135", "Data.Column136", "Data.Column137", "Data.Column138", "Data.Column139", "Data.Column140", "Data.Column141", "Data.Column142", "Data.Column143", "Data.Column144", "Data.Column145", "Data.Column146", "Data.Column147", "Data.Column148", "Data.Column149", "Data.Column150", "Data.Column151", "Data.Column152", "Data.Column153", "Data.Column154", "Data.Column155", "Data.Column156", "Data.Column157", "Data.Column158", "Data.Column159", "Data.Column1592", "Data.Column160", "Data.Column161", "Data.Column162", "Data.Column1622", "Data.Column1623", "Data.Column1624", "Data.Column1625", "Data.Column1626", "Data.Column1627", "Data.Column1628", "Data.Column1629", "Data.Column1630", "Data.Column1631", "Data.Column1632", "Data.Column1633", "Data.Column163", "Data.Column164", "Data.Column165", "Data.Column166", "Data.Column167", "Data.Column168", "Data.Column169", "Data.Column170", "Data.Column171", "Data.Column172", "Data.Column173", "Data.Column174", "Data.Column175", "Data.Column176", "Data.Column177", "Data.Column178", "Data.Column179", "Data.Column180", "Data.Column181", "Data.Column182", "Data.Column183", "Data.Column184", "Data.Column185", "Data.Column186", "Data.Column187", "Data.Column1872", "Data.Column188", "Data.Column189", "Data.Column190", "Data.Column1902", "Data.Column1903", "Data.Column1904", "Data.Column1905", "Data.Column1906", "Data.Column1907", "Data.Column1908", "Data.Column1909", "Data.Column1910", "Data.Column1911", "Data.Column1912", "Data.Column1913", "Data.Column191", "Data.Column192", "Data.Column193", "Data.Column194", "Data.Column195", "Data.Column196", "Data.Column197", "Data.Column198", "Data.Column199", "Data.Column200", "Data.Column201", "Data.Column202", "Data.Column203", "Data.Column204", "Data.Column205", "Data.Column206", "Data.Column207", "Data.Column208", "Data.Column209", "Data.Column210", "Data.Column211", "Data.Column212", "Data.Column213", "Data.Column214", "Data.Column215", "Data.Column2152", "Data.Column216", "Data.Column217", "Data.Column218", "Data.Column2182", "Data.Column219", "Data.Column220", "Data.Column221", "Data.Column2212", "Data.Column2213", "Data.Column2214", "Data.Column2215", "Data.Column2216", "Data.Column2217", "Data.Column2218", "Data.Column2219", "Data.Column2220", "Data.Column2221", "Data.Column2222", "Data.Column2223", "Data.Column222", "Data.Column223", "Data.Column224", "Data.Column225", "Data.Column226", "Data.Column227", "Data.Column228", "Data.Column229", "Data.Column230", "Data.Column231", "Data.Column232", "Data.Column233", "Data.Column234", "Data.Column235", "Data.Column236", "Data.Column237", "Data.Column238", "Data.Column239", "Data.Column240", "Data.Column241", "Data.Column242", "Data.Column243", "Data.Column244", "Data.Column245", "Data.Column246", "Data.Column2462", "Data.Column247", "Data.Column248", "Data.Column249", "Data.Column2492", "Data.Column2493", "Data.Column2494", "Data.Column2495", "Data.Column2496", "Data.Column2497", "Data.Column2498", "Data.Column2499", "Data.Column2500", "Data.Column2501", "Data.Column2502", "Data.Column2503", "Data.Column250", "Data.Column251", "Data.Column252", "Data.Column253", "Data.Column254", "Data.Column255", "Data.Column256", "Data.Column257", "Data.Column258", "Data.Column259", "Data.Column260", "Data.Column261", "Data.Column262", "Data.Column263", "Data.Column264", "Data.Column265", "Data.Column266", "Data.Column267", "Data.Column268", "Data.Column269", "Data.Column270", "Data.Column271", "Data.Column272", "Data.Column273", "Data.Column274", "Data.Column2742", "Data.Column275", "Data.Column276", "Data.Column277", "Data.Column2772", "Data.Column278", "Data.Column279"})

You need to edit the number of columns on the first row of the advanced editor. Just adding the names does not change the number of columns. Check out my other post on this thread for a screenshot.

@Seth_C_Bauer : i have the data coming from sql database. Hence i do not know where these new column will appear (in the middle or at the end). 

Is there are a way to get all columns on refersh, or instead of just a refresh is there a way to refresh the complete fetch from scrath ?

 

Thanks in Advance !! 

Anonymous
Not applicable

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

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

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

@YOURBEERGUY : I couldn't find the 'column count' at the top of advanced editor, Can you help me with a screenshot of it ?

Thanks in Advance

Top row, after the source file destination and delimiter

 

advanced editor.png

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors