Dynamically add missing columns in Power Query

In todays video I am going to show you how to add missing columns if they are missing in Power Query so your queries wont break.

Link to the resource:

Chapters:
00:00 Explain use case
00:30 Showing when you get errors
01:30 Showing the pattern and modifying it to fit our example
Done!


SUBSCRIBE to learn more about Power and Excel BI!

Our PLAYLISTS:

☼☼☼☼☼☼☼☼☼☼

POWER BI COURSES:

Want to learn Power BI? How about you take one of our courses? Here you can find the available courses:

☼☼☼☼☼☼☼☼☼☼

ABOUT CURBAL:

▼▼▼▼▼▼▼▼▼▼

If you feel that any of the videos, downloads, blog posts that I have created have been useful to you and you want to help me keep on going, here you can do a small donation to support my work and keep the channel running:


Many thanks in advance!

▲▲▲▲▲▲▲▲▲▲


************





************


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

#CURBAL #SUBSCRIBE

[jaimeildefonsosegurapena]

Ruth...
You can just use the pattern as the step and you'll get the corrected table in a single step.. as it will create the table (no need to keep 1st row, other columns remove and further expand)...
To do so just press the "fx" and type the pattern:
= if Table.HasColumns( Source, "Country code" ) then Source else
Table.Combine( {Source, Table.FromRows ({ }, {"Telephone", "Country code"}) } )

[manikandanperiasamy]

Nice one Ruth
I used the option "Insert step after " in the query editor and added the following code instead of adding as a custom column which helped to avoid the removing columns and removing rows steps.
= if Table.HasColumns( #previousstep, {"Address", "Name"}) then #previousstep
else Table.Combine({#previousstep, Table.FromRows({}, {"Address", "Name"})})

[athome]

Thank you so very much for this tutorial. I actually found this code in my searches, but was stuck once I had the new column with the tables. Your extra steps got me to the finish line.

[alejguz]

the relief in your face lol, look you were struggling like I am with some tables hopefully your solution will work for me as well, thanks for sharing!

[sebastiendebosscher]

Hi Ruth, I suggest this one step solution:
Table.Combine({Source, #table({"Name", "Surname", "Phone", "Country code"}, {})})

[HowToExcelBlog]

What I've done a few times is create an empty table with the complete set of columns, then append my data to the empty table. Any missing columns are added by the append!

[vida]

This is very useful in scenarios where number of columns can differ from original table

[nboisen]

Excellent! Now the trick would be to convert this into a function and include ALL the rows of your original source table and ALWAYS include it by default when working with merging multiple source files, since you never know when you will run into this issue along the line.

[KenLoewen]

Ruth - this is amazing! Thank you so very much for sharing and, Jaime Ildefonso SEGURA PENA, thank you also for the great enhancement to the technique.

[ahmedal-dossary]

OMG!

That’s amazing, thanks for sharing this trick!

[johnzafe]

Hi! This really helpful! Thank you thank you thank you thank you thank you so

[LuisCarlosChavez]

I appreciate your excellent video and tutorial very much. They provided me with the exact solution I needed for a long time. You have saved me a lot of time and effort in my work. Thank you for your valuable contribution.

[abhishekstatus_]

Thanks for sharing this Ruth !! Have a great week ahead!!

[subinsahadevan]

Hi, i have one question. If there are multiple sheets and some sheets has one particular column less than other sheets. Upon loading in power query it shows error, as all sheets doesn't have equal columns. Any way where we can add that missing column on the missing sheet so that upon loading it doesn't gives error?

[wiggumo]

So if my web data table has more or less columns, it will amend that automatically?

[EricaDyson]

This happened to me only the other day.. suddenly columns disappeared in the source data and messed up my queries. Now I know what to do! Thanks

[silverfunnel]

Hello, I have a question about conditional columns. Would it be possible to select a column and position the custom column after that column and not at the end? Or is it usual practice to drag it to the right position after having created it? I like your channel:-)

[arkdum]

What if I want to remove certain columns from the source tables ?
This seem to work when you want to add new...not the other way around

[Sreekanthakula]

I have developed power BI report using Power BI report using Power BI desktop free version. I have done conditional formatting in the report where in I need to apply colors for some fields in the report. I have published the reports in the power BI service.

Now after publishing in the power BI service, I need to export this report into Excel format. I am not getting option to export this report as excel, but getting option to export it as CSV file. I am not able to see coloured field in CSV file.

Could you please help me how to resolve this issue? Do I need to build this report as paginated reports? Then only I would get option to this file as excel and I can see the colors in excel file? Please suggest