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