Power Query -data transformation

sharpy137

sharpy137

New Member
Joined
Oct 8, 2015
Messages
1
I have just been upgraded to excel 2016 and have stumbled on my first attempt at using power query. I have data in an excel workbook that has a couple of rows of data sitting on its own above main data set that i would like to include in table but cannot work out solution that doesn't involve manually adding to table at the end.

Any help greatly appreciated

[TABLE="width: 1864"]
<tbody>[TR]
[TD][TABLE="width: 1864"]
<tbody>[TR]
[TD][/TD]
[TD="colspan: 11"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 11"]Network Infrastructure[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]Application number:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Application 000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]Period:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]26 - 31 May 2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]Contract Reference[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]12345[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]Contract Reference -[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]Contract Type:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] Programme[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]Contractor:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Services Ltd[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]Address for payment notification:[/TD]
[TD][/TD]
[TD][/TD]
[TD]address[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]For the attention of:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]john doe[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Assessment Date[/TD]
[TD]Cumulative[/TD]
[TD]Monthly Movement[/TD]
[TD]Previous Cumulative[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Works[/TD]
[TD]31-May[/TD]
[TD] £ 4,684,630.56[/TD]
[TD] £ 154,974.47[/TD]
[TD] £ 4,529,656.09[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Management Fee[/TD]
[TD]31-May[/TD]
[TD] £ 1,340,542.08[/TD]
[TD] £ 51,002.81[/TD]
[TD] £ 1,289,539.27[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Welfare Unit fee[/TD]
[TD]31-May[/TD]
[TD] £ 89,389.01[/TD]
[TD] £ 1,899.52[/TD]
[TD] £ 87,489.49[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Survery's completed[/TD]
[TD]31-May[/TD]
[TD] £ 136,236.64[/TD]
[TD] £ 446.03[/TD]
[TD] £ 135,790.61[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Special Works[/TD]
[TD]31-May[/TD]
[TD] £ 2,223.32[/TD]
[TD] £ -[/TD]
[TD] £ 2,223.32[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Additional Traffic Management[/TD]
[TD]31-May[/TD]
[TD] £ 234,784.13[/TD]
[TD] £ -[/TD]
[TD] £ 234,784.13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Total[/TD]
[TD] £ 6,487,805.74[/TD]
[TD] £ 208,322.83[/TD]
[TD] £ 6,279,482.91[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]£[/TD]
[TD]£[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]VAT[/TD]
[TD]Gross[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Assessment of Value of Work[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Amey Works[/TD]
[TD][/TD]
[TD][/TD]
[TD]31-May[/TD]
[TD] £ 154,974.47[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]30,994.89[/TD]
[TD="align: right"]185,969.36[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Amey Management Fee[/TD]
[TD][/TD]
[TD][/TD]
[TD]31-May[/TD]
[TD] £ 51,002.81[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10,200.56[/TD]
[TD="align: right"]61,203.37[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Welfare Unit as per new rates[/TD]
[TD][/TD]
[TD]31-May[/TD]
[TD] £ 1,899.52[/TD]
[TD]Welfare Unit for May (2 gangs)[/TD]
[TD][/TD]
[TD][/TD]
[TD]379.90[/TD]
[TD="align: right"]2,279.42[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Survery's completed[/TD]
[TD][/TD]
[TD][/TD]
[TD]31-May[/TD]
[TD] £ 446.03[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]89.21[/TD]
[TD="align: right"]535.24[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Special Works[/TD]
[TD][/TD]
[TD][/TD]
[TD]31-May[/TD]
[TD] £ -[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Addition works[/TD]
[TD][/TD]
[TD][/TD]
[TD]31-May[/TD]
[TD] £ -[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Additional Traffic Management[/TD]
[TD][/TD]
[TD]31-May[/TD]
[TD] £ -[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD="align: right"]0.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]ref number[/TD]
[TD]House Number[/TD]
[TD]Address[/TD]
[TD]Date completed[/TD]
[TD]Actualy Install Type[/TD]
[TD]Length (m)[/TD]
[TD]Area[/TD]
[TD]New Boundary Box[/TD]
[TD]New Ferrule[/TD]
[TD] Cost[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]015/0/00530/0430[/TD]
[TD]43-46[/TD]
[TD]Road name[/TD]
[TD]05/05/2017[/TD]
[TD]Moling[/TD]
[TD]5[/TD]
[TD]Town b[/TD]
[TD][/TD]
[TD][/TD]
[TD] £ 759.85[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]015/0/00530/0510[/TD]
[TD]51-59[/TD]
[TD]Road name[/TD]
[TD]05/05/2017[/TD]
[TD]Moling[/TD]
[TD]5[/TD]
[TD]Town b[/TD]
[TD][/TD]
[TD][/TD]
[TD] £ 759.85[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]018/0/00190/0270[/TD]
[TD]27-29[/TD]
[TD]Road name[/TD]
[TD]05/05/2017[/TD]
[TD]Moling[/TD]
[TD]10[/TD]
[TD]Town b[/TD]
[TD][/TD]
[TD][/TD]
[TD] £ 1,118.08[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]018/0/00190/0310[/TD]
[TD]31-33[/TD]
[TD]Road name[/TD]
[TD]05/05/2017[/TD]
[TD]Moling[/TD]
[TD]7[/TD]
[TD]Town b[/TD]
[TD][/TD]
[TD][/TD]
[TD] £ 778.93[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]018/0/00190/0350[/TD]
[TD]35[/TD]
[TD]Road name[/TD]
[TD]05/05/2017[/TD]
[TD]Moling[/TD]
[TD]10[/TD]
[TD]Town b[/TD]
[TD][/TD]
[TD][/TD]
[TD] £ 1,118.08[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]019/0/00355/0220[/TD]
[TD]22[/TD]
[TD]Road name[/TD]
[TD]12/05/2017[/TD]
[TD]Moling[/TD]
[TD]4[/TD]
[TD]Town b[/TD]
[TD][/TD]
[TD][/TD]
[TD] £ 750.31[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]019/0/00355/0180[/TD]
[TD]18[/TD]
[TD]Road name[/TD]
[TD]12/05/2017[/TD]
[TD]Moling[/TD]
[TD]4[/TD]
[TD]Town b[/TD]
[TD][/TD]
[TD][/TD]
[TD] £ 750.31[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]019/0/03355/0181[/TD]
[TD]18A[/TD]
[TD]Road name[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="colspan: 11"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
You must log in or register to reply here.

Similar threads

M
  • Question Question
Power Query Get Transform the the in Tabular Format
2
Replies
10
Views
1K
sandy666
sandy666
N
  • Question Question
Managing Payment table separate from billing, calculating lps on every received payment based on criteria.
Replies
1
Views
601
NaushadAnwar
N
J
  • Question Question
VBA to extract row data to sheet and update data set
Replies
0
Views
953
justatroy
J
M
  • Solved
Update row selects first row of table instead of correct row
Replies
6
Views
1K
Alex Blakenburg
A
T
  • Question Question
Subscription/Service Payment Calculator - Active/Cancelled and Pro-rata Payments
Replies
0
Views
752
Thompa121
T

Forum statistics

Threads
1,224,824
Messages
6,181,186
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top