Transposing problem

Now land results into the excel sheet. Open right pane and right click on first query name, select Load to, here is Table and location where you'd like to keep it

Right click on any cell within the table and select External table properties

within it deselect Adjust column width.

Now you may add bit cosmetic to your table - change design and format of columns / headers.

With changing of source data click Refresh in right click menu of the table or in Data section of ribbon.

File with above steps is attached.

As a comment, resulting data is bit incorrect. You have

- !DIV/0 errors within the source data,

- repeating column names (e.g. Return 5-2012 and some else repeated two times). It shall be corrected in source (all names are to be unique) or processed additionally within the query.

- empty columns in the middle;

- perhaps something else

But that better test on copy of real data.

Let continue with static information. Open in Excel right pane with queries, right click on your first query and Duplicate. Rename it (let say on Static) and Close and Load to -> Only create connection.

Open it again on editing and remove all steps from the bottom one by one (click on cross at the left on step name) keeping Filtering Rows as the last available step

Stay on that step, expand formula bar and within it change manually every "Text.Start..." on "not Text.Start..." and every "or" on "and", click Enter after that.

Final formula shall be as

collapse formula bar.

Select Attribute column (second one) and pivot it with Value column without aggregation.

That's all with this query.

Now return back to the editing of first query. After you opened it when Home->Append Queries

and select second query to append

Now sort ascending Name column and after that sort ascending Date column

Close query editor and keep changes.

To continue with query editing in excel ribbon click on

in opened right pane right click on query name and select Edit. You are in query editor now. On the right you'll see two generated steps, delete second one

select all columns like in Excel (first column, Shift, last column) and in editor ribbon click on

and replace on null

Now select only first Name column and

Text filter second column only with records needed for the left part of your resulting table

Now we shall split text and dates in your second column. Let remove the text after the date first. Select column and do

and replace USD on nothing (don't forget the space before USD)

Select second column and split it

by right space

Most probably third column will automatically changed on Date type, you will see one more step

If not - select it and Home->Data type->date.

Now select all columns and

After that select second column and drag it to the left on first position

Keep it selected and pivot

without aggregation on Value

Rename columns where necessary (double click on header and change the text).

That's all with this query, close the editor and save workbook.