Merge Tables into aligned row

[brad58]

Hi all,

I've managed to get the following code to collate all my information into the same table (three different tables picked up), however when outputting the data it creates a new row, for each table (i.e. 3 rows for the one line).

How can I ensure these are combined with the following code? Or Updating the following code:

let GetResults=(URL_List) =>

let
Source0 = Web.Page(Web.Contents(URL_List)),
Data0 = Source0{0}[Data],
#"Changed Type" = Table.TransformColumnTypes(Data0,{{"Column1", type text}, {"Column2", type text}}),
#"Transposed Table" = Table.Transpose(#"Changed Type"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Trainer", type text}, {"Home Track", type text}, {"Colour", type text}, {"Age/Sex", type text}, {"Prize Money", Currency.Type}}),

Source1 = Web.Page(Web.Contents(URL_List)),
Data1 = Source1{1}[Data],
#"Changed Type2" = Table.TransformColumnTypes(Data1,{{"Column1", type text}, {"Column2", type text}}),
#"Transposed Table1" = Table.Transpose(#"Changed Type2"),
#"Promoted Headers1" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
#"Changed Type3" = Table.TransformColumnTypes(#"Promoted Headers1",{{"Foal Date", type text}, {"Sire", type text}, {"Dam", type text}, {"Sire of Dam", type text}}),

Source2 = Web.Page(Web.Contents(URL_List)),
Data2 = Source2{2}[Data],
#"Changed Type4" = Table.TransformColumnTypes(Data2,{{"Career", type text}, {"Last 10 Results", type text}, {"Season", type text}, {"Rating", Int64.Type}, {"ROI", Percentage.Type}}),

#"TableCombined" = Table.Combine({#"Changed Type1", #"Changed Type3",#"Changed Type4"})

in

#"TableCombined"

in GetResults

Data currently comes out like following:

Col 1   Data 1 Data 2 Data 3

Link 1  This

Link 1             Should

Link 1                         Combine

Link 2  But

Link 2              It

Link 2                          Doesn't

[Ashish_Mathur]

Hi,

Paste your input data here.

[brad58]

Hi Ashish,

This is a link I'm using, but I'll end up with a larger URL list as per the code to extract them all.

https://www.racenet.com.au/horse/lankan-rupee

Basically pulling in the 3 tables it identifies:

Table1

Table2

Table3

Trainer

Home Track

Colour

Age/Sex

Prize Money

Foal Date

Sire

Dam

Sire of Dam

Career

Last 10 Results

Season

Rating

ROI

[Ashish_Mathur]

Hi,

Does this help?  You may download my file from here.

[brad58]

Hi Ashish,

Can you Transpose the data to have the Headers across the top, and the data underneath it (i.e. row 1 = Headers, row 2 = data).

I downloaded your sheet then and did that but had the same issue with the tables not lining up by row, as there are null values in the column headers where the other tables dont have data for it.

Link to file I've completed here

Ive included 3 example linkes in Sheet 3 that eventually I'd turn into a table, and then have the powerquery go through every link and return the data we have listed.

[Ashish_Mathur]

Hi,

I do not understand your requirement.  Show me the exact result you want.  Also, that link requires signing in.

[brad58]

Hi Ashish,

I basically want your result but transposed so headers are across the columns, and data in the row beneath.
My problem is the data comes out with a row for each table result and not all three tables in the one row.

[Ashish_Mathur]

Hi,

Right click on the Append Query > Edit.  Select both columns and go to Transform > Transpose.  Then Promote the Headers.

[brad58]

Hi,

Ok thats the format I want it in, but now I want to be able to paste a list of URL's that the query will run through and add the data from those 3 tables into the one combined table below each other.

Hopefully this link works, as this is what I had done previously with this file being your with the latest transposing, and this file being the original one I did with the row errors.

Let me know if those links don't work I'm uploading into Dropbox.

[v-juanli-msft]

Hi @brad58

First i need to confirm requirements with you

your expected output should be:

Right?

Best Regards

Maggie