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"}) } )
@@CurbalEN I once had a similar problem... and had to figured out my self... Where did you find the pattern as it will be useful to have a help source for checking next time?
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!
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"})})
This is much cleaner, and it works well. I can list all the columns that need to be there, and list them all in the needed, rather than having several steps to check each column.
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?
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.
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.
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:-)
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.
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
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.
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"}) } )
Fabulous! Why didnt I think of that? Thanks a million :)))
@@CurbalEN I once had a similar problem... and had to figured out my self... Where did you find the pattern as it will be useful to have a help source for checking next time?
The link is in the description box :)
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!
Yes, thatbis also an option:)
Hi Ruth, I suggest this one step solution:
Table.Combine({Source, #table({"Name", "Surname", "Phone", "Country code"}, {})})
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"})})
This is much cleaner, and it works well. I can list all the columns that need to be there, and list them all in the needed, rather than having several steps to check each column.
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?
So if my web data table has more or less columns, it will amend that automatically?
OMG!
That’s amazing, thanks for sharing this trick!
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.
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.
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
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:-)
This is very useful in scenarios where number of columns can differ from original table
Hi! This really helpful! Thank you thank you thank you thank you thank you so much!!!!!!
🥳🥳🥳
Your body language is good
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.
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
Use analyze in excel instead. You will have to rebuild the report again in excel, but it should work after that.
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!
😂
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.
Exactly!
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
Use the pattern as a step to avoid the tables!
M is magic! Thanks Ruth
Great job! I always watch your videos
Thanks for the support!
Another cracking tip Ruth 😂🙂😄
I mossed the mark with this one, it should be used as a new step not a add column 🙈🙈🙈
Live and learn...
Nice!
Super!!
Thanks for sharing this Ruth !! Have a great week ahead!!
You too!
Thanks for sharing!👍
My pleasure :)