Removing Multiple Columns in Excel | Power Query

To remove or delete blank columns in an Excel table is fairly straight forward and easy. Just select the column or press Ctrl to select multiple columns to delete. However if there might be a LOT of columns and they might be non consecutive (i.e., not next to each other). You may have received this table from a system, person or some other source that has just not thought about you as a end user (the table has twenty or more empty columns EVERYWHERE) and it's going to be a lot of clicking. There are a couple a ways this could be solved and it doesn't involved writing VBA. There is a little bit of M code to write in one of the examples, but it is not that much considered its usefulness. See the video now to see how it's done.

Import SharePoint File into Power Query

Importing a Excel file that is hosted on SharePoint or OneDrive should be as easy as copying the URL from the browser or when you use the share feature in SharePoint / OneDrive. Unfortunately, it's not so straightforward. First, the URL link from the share feature isn't event the right one to use and then you have to be on the online version and request to open it with the desktop version to get the correct URL. After that it's just a copy and paste of an edited version of the URL to put into the Power Query field. It's sounds like a lot but once you see how it's done, it's not so bad. Hopefully Microsoft will make it easier in future Power Query updates. But in the meantime, check out the video to learn how to import a file hosted on SharePoint into Power Query.

Solve the Formula.Firewall Error in Power Query

The Formula Firewall error is a data privacy error you might get when sharing Power Query Excel files and it's basically Excel's way of saying you have an external data source when it seems like you don't. One way to mitigate this is to change the privacy setting to ignore this message, but when you're collaborating with other, they might not feel comfortable doing this. The other option is to set up a bridge or staging querying. This involves getting into the M code, but it's not so bad. This video talks about the table that takes in user input on where the folder path is for a file but those detail instructions are in another video at https://youtu.be/ignTb_UozTw. This one will cover mostly on how to solve the Formula Firewall issue.

Share Power Query File - Formula.Firewall Error Workaround

When you're sharing files that use Power Query and it sources a data file from a separate workbook, you might come across multiple issues like the DataSource.Error or Formula.Firewall error message. A way to get around these issues is to ask the user you're working with to change or update a few settings. It's not too complicated so check out the video to learn how.

How to Fix the DataSource.Error Message in Power Query

If you're sharing or collaborating with other intermediate Power Query users and source data from large files, you don't want to send those large files back and forth if it doesn't change. Basically your transformation or analysis file is the changing item and when you share that file, the source for the data is going to change; hence you might get the dreaded DataSource.error message. There's a couple ways to deal with this error like replacing the source in the query steps and then refreshing. In this video, it'll cover a way to use the parameter feature in Power Query to set up a path to the source and reference that path in the query step.

Convert Multiple Line Records into Different Rows

Sometimes you get a list that's in a two column name value or attribute value format and need to split these multiline records into separate rows. If this were a small table you could do a copy/paste or create some formulas to pull out the name value pairs. If this were a recurring process you might even write some VBA. Well I'm no VBA guru but this can be done with Power Query and then be used again. If you get these type of tables often and have to perform these steps on a regular basis just overwrite or append the new data and refresh the query like it's magic! 🧙‍♂️

Combine Tables with Header Values in Different Rows

Excel can be used to to combine or append tables in multiple different worksheet tabs in the same workbook file. But there may also be a value that you'd want to preserve in those worksheets to use for a new column value in the combined output. The tables are all structured the same and have the same column headings but the worksheet tabs have the generic name (i.e, Sheet1 or Sheet2). The common field in each worksheet can be used as a value that categorizes the tables. One worksheet will be California data and another worksheet would be New York data. However there's a catch...the header for those state are in different rows for each sheet. If these were small data sets, then copy/paste and some column movements would suffice. However if these are large tables, you do this on a recurring basis or you had 10 or 20 worksheets in the file, it would be a chore. This is a great opportunity to use Power Query and this video will show how. This is a tip I learned from Gil Raviv's book. Get the book at https://amzn.to/2Zj9ADY