Forum Discussion

brad58's avatar
brad58
Regular Visitor
7 years ago

Merge Tables into aligned row

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

9 Replies

    • brad58's avatar
      brad58
      Regular Visitor

      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