Need help on Power Query: how to shape this excel import into a proper table

Hi seanwu ,

As it's such a small table I would probably split it into three tables then append them all together.

Select your columns 1 & 2, right-click on one of the selected column headers and hit Remove Other Columns.

Duplicate this table, delete the remove columns step, but do it again selecting columns 3 & 4.

Same again for 5 & 6.

In each of your new tables, make sure the description columns are all called the same in each table (including case!), and the same for the values columns. Also make sure data types match in the same way.

Select your column 1 & 2 table, go to Home tab > Append Queries (dropdown button) > Append Queries as New.

Choose option to append more than two tables, then add your 3 & 4 and 5 & 6 tables to the append list and hit OK.

You can now right-click your original smaller queries and uncheck Enable Load on each of them. This will ensure you only pass the appended table to the data model but also ensure that they update correctly when you refresh.

I'm sure I could come up with a super-whizzy way to do this in M code, but this is probably the quickest/simplest if you want to stick to using the GUI.

Pete