Forum Discussion
Solved
Table Merge from Web
Dear Experts,
I need to export the below in a Single Table from below link :-
https://portal.3gpp.org/desktopmodules/Specifications/SpecificationDetails.aspx?specificationId=3283
But when I use PQ, it gives me into different Tables, and I have to combine them Manually as below:-
How shall , I merge them all into a Single Output?
Thanks in Advance,
Br,
Anupam
When you first select the desired tables to be imported, click the drop-down arrow beside "Load" and select "Load To...", then select "Only Create Connection" and click "OK".
If you've already imported these tables into your workbook, go to Data > Queries and Connections, right-click on each table query, select "Load To..." and change it to "Only Create Connection".
After all of the desired tables have been imported as "Connection only", go to Data > Get Data > Launch Power Query Editor..., select the first table query in the left-hand pane, then go to Home > Append Queries > Append Queries as New and add the applicable tables to append.
From there you can modify the query as needed to clean the data, then "Close and Load" to a table in the workbook. Please see the attached sample file, if needed...
5 Replies
When you first select the desired tables to be imported, click the drop-down arrow beside "Load" and select "Load To...", then select "Only Create Connection" and click "OK".
If you've already imported these tables into your workbook, go to Data > Queries and Connections, right-click on each table query, select "Load To..." and change it to "Only Create Connection".
After all of the desired tables have been imported as "Connection only", go to Data > Get Data > Launch Power Query Editor..., select the first table query in the left-hand pane, then go to Home > Append Queries > Append Queries as New and add the applicable tables to append.
From there you can modify the query as needed to clean the data, then "Close and Load" to a table in the workbook. Please see the attached sample file, if needed...
Thank you so much JKPieterse
Please provide a little more detail: how exactly did you get these "Table 36", Table 33", etc. tabs in an Excel file? Which steps did you follow?
Hi JKPieterse , I did below:-
let
Source = Web.BrowserContents("https://portal.3gpp.org/desktopmodules/Specifications/SpecificationDetails.aspx?specificationId=3283"),
#"Extracted Table From Html" = Html.Table(Source, {{"Column1", "LI.rpItem:nth-child(2) > DIV.rpSlide > DIV.rpTemplate > DIV.TabContent > DIV.RadGrid.RadGrid_Default:nth-child(2) > DIV.rgDataDiv > TABLE.rgMasterTable.rgClipCells > * > TR > :nth-child(1)"}, {"Column2", "LI.rpItem:nth-child(2) > DIV.rpSlide > DIV.rpTemplate > DIV.TabContent > DIV.RadGrid.RadGrid_Default:nth-child(2) > DIV.rgDataDiv > TABLE.rgMasterTable.rgClipCells > * > TR > :nth-child(2)"}, {"Column3", "LI.rpItem:nth-child(2) > DIV.rpSlide > DIV.rpTemplate > DIV.TabContent > DIV.RadGrid.RadGrid_Default:nth-child(2) > DIV.rgDataDiv > TABLE.rgMasterTable.rgClipCells > * > TR > :nth-child(3)"}, {"Column4", "LI.rpItem:nth-child(2) > DIV.rpSlide > DIV.rpTemplate > DIV.TabContent > DIV.RadGrid.RadGrid_Default:nth-child(2) > DIV.rgDataDiv > TABLE.rgMasterTable.rgClipCells > * > TR > :nth-child(4)"}, {"Column5", "LI.rpItem:nth-child(2) > DIV.rpSlide > DIV.rpTemplate > DIV.TabContent > DIV.RadGrid.RadGrid_Default:nth-child(2) > DIV.rgDataDiv > TABLE.rgMasterTable.rgClipCells > * > TR > :nth-child(5)"}, {"Column6", "LI.rpItem:nth-child(2) > DIV.rpSlide > DIV.rpTemplate > DIV.TabContent > DIV.RadGrid.RadGrid_Default:nth-child(2) > DIV.rgDataDiv > TABLE.rgMasterTable.rgClipCells > * > TR > :nth-child(6)"}, {"Column7", "LI.rpItem:nth-child(2) > DIV.rpSlide > DIV.rpTemplate > DIV.TabContent > DIV.RadGrid.RadGrid_Default:nth-child(2) > DIV.rgDataDiv > TABLE.rgMasterTable.rgClipCells > * > TR > :nth-child(7)"}, {"Column8", "LI.rpItem:nth-child(2) > DIV.rpSlide > DIV.rpTemplate > DIV.TabContent > DIV.RadGrid.RadGrid_Default:nth-child(2) > DIV.rgDataDiv > TABLE.rgMasterTable.rgClipCells > * > TR > :nth-child(8)"}}, [RowSelector="LI.rpItem:nth-child(2) > DIV.rpSlide > DIV.rpTemplate > DIV.TabContent > DIV.RadGrid.RadGrid_Default:nth-child(2) > DIV.rgDataDiv > TABLE.rgMasterTable.rgClipCells > * > TR"]),
#"Changed Type" = Table.TransformColumnTypes(#"Extracted Table From Html",{{"Column1", type text}, {"Column2", type text}, {"Column3", type date}, {"Column4", type text}, {"Column5", type text}, {"Column6", Int64.Type}, {"Column7", type text}, {"Column8", Int64.Type}})
in
#"Changed Type"
Use data in Excel → Get Data → Import the table from the Web, through the Power Query editor associated with multiple tables of data, and finally point to close and upload the integration can be completed!
