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.

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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

san_jois's avatar
san_jois
Icon for Resolver I rankResolver I
9 years ago

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

  • monojchakrab's avatar
    monojchakrab
    Icon for Resolver III rankResolver III
    4 years ago

    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 number