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

[seanwu]

Hi,

I need help on power query. I have an Excel file that I imported into PQE. It is like this:

As you can see, column 1, 3, 5 are names of commission rates. column 2,4,6 are values of commission rates. Obviouly this is not a proper format for tables. How can I move column 3/4 and 5/6 to the end of column 1/2 so that I have two columns, one is commission rates names and the other is commission values. Hopefully this can be achieved by using only the GUI.

Thanks, 

Sean

[BA_Pete · Accepted solution]

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

[seanwu]

Thanks Pete. That works.