Avoid refresh errors with missing columns in Power BI/Power Query

Have you refreshed Power BI files and got errors due to missing columns or unwanted columns? Here I show you how to avoid new columns for getting added and refresh error from missing columns.

Make sure you watch it too!

Chapters:
00:00 Intro + Chris webb webinar
01:50 Show when/how you get errors in power query
03:35 Create an empty table with the columns needed
04:35 Combine empty table with data table
Done!

Happy analyzing!

SUBSCRIBE to learn more about Power and Excel BI!

Our PLAYLISTS:

ABOUT CURBAL:

QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
► Twitter: @curbalen, @ruthpozuelo

#SUBSCRIBE #CURBAL

[Chris_in_fremantle]

Eight years after being published and Ruth saves my bacon, deep into a Friday afternoon. Thanks Ruth.

[apamwamba]

I solved this missing columns issue by accident but this tip has explained why my solution worked. I was trying to align the columns e.g. ID with 1.ID, AGE with 2.AGE so i appended a custom column with 1 row and got rid of the unwanted column. It worked!! Thanks for the explanation. Wonderful resource!!

[MindThePoliticalGap]

Hi Rita, just to add a note... There is a slight error in the Video (although this is old - its being referenced daily i am sure) after adding the Combine table (5:29), the change type was refering to the #"Promote Headers" - and should have should have been removed. That why it caused the error the second time you tested it. You solved this by removeing it and adding it back, as the socond one would have referred to the Combine table. Maybe an edit might be nice to say... dont forget to remove the old Change Type:-)

[razvanyke]

Top Video, many thanks for posting. I had the annoying "column not found" error, but with "empty" DAX command has been solved. Lots of health : ) !

[jeniferdefigueiredo]

What if a column or two is/are added to your spreadsheet that you are bringing in and you want them to come into PowerBI? Do you just follow the first steps for missing column, but include the columns names of the incoming that you do want?

[Phoenixspin]

I don't like to feel hate but I really hate refresh errors. They can just ruin your day. This gives me a tool to fight back.

[seamushand]

Thank this is very useful. Note that I have also found that if you wrap the Column name ( using Alt+Enter) eg to make "Expected month to Invoice" fit in a narrower column that PQ just picks up the title on the first line, eg "Expected" and then reports that it cannot find "Expected month to Invoice" !

[shubhamsharma]

Ma'am i have a error in my data from online source it's say "column(xyz) was not found", do you have any solution

[saviomiranda]

This is awesome, you just saved me an entire day of creating a new table with nearly 30 calculated colums all over again.

[sudherkumar]

Can I use {Missing field ignore} formula in power query for this problem?

[kimJB]

Thanks! Does this works with Folders...using the tip Csv.Document ([Content])? Not quite sure since the first file usually defines the headers.

[skipa]

How do I make it to read additional columns is I want to add more columns to my XLSX file?

[darsh]

Woow!! This is amazing, what a neat trick. Thank you so much for sharing

[claudiorebelo]

Very clever and useful. Strange that there is not a native way to deal with this.

[ankitadharmik]

Thank you so much for this video.. it's very useful... Much needed this... Good content, easy to understand... Thnx...

[KB-wbym]

Hi Ruth, such a great video! Does this video still hold good as of today or is there any new functionality/features Microsoft has come up with in Power BI to deal with this organic requirement? Thanks & regards, Bhoga

[maddy]

Hi. Thanks a lot. This video has been extremely helpful.

[gkakun]

Brilliant! And if the user changed one of the columns? in your example, instead of Category, he uploaded "Categories" and I still import this file- do I have a way to deal with this?

[muhammedashraf]

Hi, I am getting a error message when I try to Publish --> " an error occurred while attempting to publish PBIX File publishing failed for unknow reason".  could you pls help on this.

[leanh]

at 6:51 it doesnt work I think the reason is not the type, it's because that you dont refer to the right step (in your case, you refered to the Promoted Headers step instead of Combine)


Just a small thing, but great video!
THanks,