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

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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
590
NaushadAnwar
N
J
  • Question Question
VBA to extract row data to sheet and update data set
Replies
0
Views
940
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
750
Thompa121
T

Forum statistics

Threads
1,223,908
Messages
6,175,307
Members
452,633
Latest member
DougMo

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