Refresh powerBI data with additional column

Question

I have built a powerBI dashboard with data source from Datalake Gen2. I am trying to add new column into my original data source. How to refresh from PowerBI side without much issues or whats the best way to do?

score 12 · Answer 1 · edited Jan 26 '22 at 00:06

12

You can configure the query so that the number of columns is dynamic by setting Columns=null.

These instructions get you to the same place as @Janzaib's answer, but are more intuitive for me.

In Power BI Desktop, right-click on your dataset under Fields ('VoterList' in this case), and select Edit query:
In Power Query Editor, select Advanced Editor:
In Advanced Editor, increment the Columns value:

Note: If you increment this value larger than your data set, you will end up with empty columns.

However, if you set Columns=null, then it will import all of the columns found in your source automatically, even as your source changes.

edited Jan 26 '22 at 00:06

Jeremy Caney

7,102
69
48
77

answered Jan 25 '22 at 17:02

Chris Harris

219
2
6

This worked for me. But this is such a bad design on MS part. – GenDemo May 13 '22 at 04:28
Opening the advanced query editor did not give me any column parameter. However, when using Janzaib M Baloch's answer in combination with this answer, it worked. So I accessed the advanced editor through Janzaib's answer to see the columns parameter, but implemented the 'null' solution from this answer. – Jem Aug 03 '22 at 10:03

score 9 · Accepted Answer · answered Feb 10 '20 at 08:40

9

You can add the column in your new data source, when Power BI refreshes against the data set you will NOT see it in report designer. You will have to go into the Query editor, select the dataset & refresh the preview. It will then pick up the new column. It will now show in the report designer.

Hope that helps

answered Feb 10 '20 at 08:40

Jon

4,593
3
13
33

Is there no way to have it automatically done? – Wouter Feb 18 '21 at 08:19
@Wouter - no you have to manually confirm the schema change – Jon Feb 18 '21 at 08:34
For me, it doesn't automatically update. This is such a bad design flaw! – GenDemo May 13 '22 at 04:27
for me even that does not work tbh. have delete the model and add it again – euh Oct 21 '22 at 12:07

score 4 · Answer 3 · answered Feb 26 '21 at 21:26

When you refresh you table and your data has new columns into it. Yes, it will give you issues. Or your new columns will just not show up.

Here is how you can fix it. Go to Transform Data and select transform data option

Look for "Transform Sample File" on left hand side and select it

Select advanced editor on top center and you should see something like this. and simply increase the number of columns your sample file can possibly contain.

Refresh powerBI data with additional column

3 Answers3