Red Gig

When working with spreadsheets and tables within spreadsheets it can be common for the column names to change. This can be deliberate or accidental…. Fortunately there is a transformation in the Power Query interface that can deal with column header issues easily. Note - if the order of the columns change this wont work! In this example I have a table with 3 columns as shown here The associated query includes a transformation to create a new column with 10% of the price, called Fee. As you can see the column titled Price is included in the formula to create the new 'Fee' column. If for any reason this column changes then this formula will fail and present an error. To demonstrate this I'll change the column title to 'Price ($)' and refresh the query. As expected, the Fee column returns an error as the Price column has changed. The error details the following issue The first line of the error clearly states the Price of the record wasn’t found. How to fix this issue ...

A recent addition to the 'split by delimiter' options allows a column to be split to rows as opposed to columns. This has a number of benefits but it can also be used to insert blank rows into a dataset. Update - to do this with multiple columns there are some additional steps, I've covered these at the bottom of this post. For example, if we had a list of colours and we wanted to add 2 blank rows between each change of colour Can be transformed to become… This is how its done. First, load the data to Power Query. For this example I've used the 'enter data' function to directly paste it into the data model. You can use the 'get and transform function in Excel to load a CSV or Excel file as required. Then, add an index from 0 and change the column name to 'Primary' Next we'll add another index from 1 and change the column name to 'Secondary' Once these are added, we'll use the 'Merge' function to merge the table with itself usi...

Word and Excel don’t usually get along too well so it's no surprise that Power Query isn't directly compatible with its estranged cousin Word either. If you are presented with the need to import data from Word into Power Query you'll be please to hear it is possible however it requires a couple of manual steps to make it work.  The manual steps could fairly easily be completed by a batch file which would automate the process further. Here is the Excel data pasted 'as values' in a Word file which i'll use for the first example Here is the Excel data pasted with 'keep source formatting' which i'll reference a couple of times in the article. Although the steps I've covered below aren't complex, this whole process has some unknowns around it so you may find the result in your instance varies from mine. The Word file I've used contains the contents of a range of excel cells that I deliberately pasted as values into Word to create a test file f...