locked
PowerQuery - Web - Create function of function on multiple tables RRS feed

  • Question

  • I have a web query that brings in 18 tables 0-17 all even rows are descriptions and ids of the odd tables. 

    The steps I am trying to perform

    1. tidy up even rows and insert index to relate to odd tables.

    2. merge all tidy even tables together as 1 query

    3. merge all odd numbered tables and update index from related tidy table.

    close and load.

    First though I need to create a function of the first tidy to implement on the subsequent tables and then save the code and then merge the tables and save that code as a function so I can reuse this on later web imports.

    This is the code that tidies the first table.

    let    Source = Web.Page(Web.Contents("http://racing.racingnsw.com.au/FreeFields/Results.aspx?Key=2015Oct31,NSW,Rosehill%20Gardens")),    Data0 = Source{0}[Data],    #"Reversed Rows" = Table.ReverseRows(Data0),    #"Reversed Rows1" = Table.ReverseRows(#"Reversed Rows"),    #"Reversed Rows2" = Table.ReverseRows(#"Reversed Rows1"),    #"Demoted Headers" = Table.DemoteHeaders(#"Reversed Rows2"),    #"Transposed Table" = Table.Transpose(#"Demoted Headers"),    #"Added Index" = Table.AddIndexColumn(#"Transposed Table", "Index", 0, 1),    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "Column1", "Column2"}),    #"Split Column by Delimiter" = Table.SplitColumn(#"Reordered Columns","Column2",Splitter.SplitTextByDelimiter(" "),{"Column2.1", "Column2.2", "Column2.3", "Column2.4", "Column2.5", "Column2.6", "Column2.7", "Column2.8", "Column2.9", "Column2.10", "Column2.11", "Column2.12", "Column2.13", "Column2.14", "Column2.15", "Column2.16", "Column2.17", "Column2.18", "Column2.19", "Column2.20", "Column2.21", "Column2.22", "Column2.23", "Column2.24", "Column2.25", "Column2.26", "Column2.27", "Column2.28", "Column2.29", "Column2.30", "Column2.31", "Column2.32", "Column2.33", "Column2.34", "Column2.35", "Column2.36", "Column2.37", "Column2.38", "Column2.39", "Column2.40", "Column2.41", "Column2.42", "Column2.43", "Column2.44", "Column2.45", "Column2.46", "Column2.47"}),    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column2.1", type text}, {"Column2.2", type text}, {"Column2.3", type text}, {"Column2.4", type text}, {"Column2.5", type text}, {"Column2.6", Int64.Type}, {"Column2.7", type text}, {"Column2.8", Int64.Type}, {"Column2.9", type text}, {"Column2.10", Int64.Type}, {"Column2.11", type text}, {"Column2.12", Int64.Type}, {"Column2.13", type text}, {"Column2.14", Int64.Type}, {"Column2.15", type text}, {"Column2.16", Int64.Type}, {"Column2.17", type text}, {"Column2.18", Int64.Type}, {"Column2.19", type text}, {"Column2.20", Int64.Type}, {"Column2.21", type text}, {"Column2.22", Int64.Type}, {"Column2.23", type text}, {"Column2.24", type text}, {"Column2.25", type text}, {"Column2.26", type text}, {"Column2.27", type text}, {"Column2.28", type text}, {"Column2.29", type text}, {"Column2.30", type text}, {"Column2.31", type text}, {"Column2.32", type text}, {"Column2.33", type text}, {"Column2.34", type text}, {"Column2.35", type text}, {"Column2.36", type text}, {"Column2.37", type text}, {"Column2.38", type text}, {"Column2.39", Int64.Type}, {"Column2.40", type text}, {"Column2.41", type text}, {"Column2.42", type text}, {"Column2.43", type text}, {"Column2.44", type text}, {"Column2.45", type text}, {"Column2.46", type text}, {"Column2.47", type text}}),    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column2.31", "Column2.32", "Column2.33", "Column2.34", "Column2.35", "Column2.36", "Column2.37", "Column2.40", "Column2.45", "Column2.46", "Column2.47", "Column2.42", "Column2.43", "Column2.25", "Column2.26", "Column2.27", "Column2.28", "Column2.29", "Column2.30", "Column2.17", "Column2.18", "Column2.19", "Column2.20", "Column2.21", "Column2.22", "Column2.23", "Column2.24", "Column2.10", "Column2.11", "Column2.12", "Column2.13", "Column2.14", "Column2.15", "Column2.16", "Column2.4", "Column2.5", "Column2.6", "Column2.7", "Column2.8", "Column2.2", "Column2.3", "Column2.9", "Column2.1"}),    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column2.38", "Condition"}, {"Column2.39", "TrackRating"}, {"Column2.41", "Time"}, {"Column2.44", "600m"}}),    #"Split Column by Delimiter1" = Table.SplitColumn(#"Renamed Columns","Column1",Splitter.SplitTextByDelimiter("-"),{"Column1.1", "Column1.2"}),    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Column1.1", type text}, {"Column1.2", type text}}),    #"Split Column by Delimiter2" = Table.SplitColumn(#"Changed Type1","Column1.2",Splitter.SplitTextByEachDelimiter({" "}, null, false),{"Column1.2.1", "Column1.2.2"}),    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Column1.2.1", type text}, {"Column1.2.2", type text}}),    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type2",{"Column1.2.1"}),    #"Split Column by Delimiter3" = Table.SplitColumn(#"Removed Columns1","Column1.2.2",Splitter.SplitTextByEachDelimiter({" "}, null, false),{"Column1.2.2.1", "Column1.2.2.2"}),    #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter3",{{"Column1.2.2.1", type time}, {"Column1.2.2.2", type text}}),    #"Cleaned Text" = Table.TransformColumns(#"Changed Type3",{{"Column1.2.2.2", Text.Clean}}),    #"Split Column by Delimiter4" = Table.SplitColumn(#"Cleaned Text","Column1.2.2.2",Splitter.SplitTextByDelimiter("("),{"Column1.2.2.2.1", "Column1.2.2.2.2"}),    #"Changed Type4" = Table.TransformColumnTypes(#"Split Column by Delimiter4",{{"Column1.2.2.2.1", type text}, {"Column1.2.2.2.2", type text}}),    #"Trimmed Text" = Table.TransformColumns(#"Changed Type4",{{"Column1.2.2.2.2", Text.Trim}}),    #"Split Column by Delimiter5" = Table.SplitColumn(#"Trimmed Text","Column1.2.2.2.2",Splitter.SplitTextByDelimiter(")"),{"Column1.2.2.2.2.1", "Column1.2.2.2.2.2"}),    #"Changed Type5" = Table.TransformColumnTypes(#"Split Column by Delimiter5",{{"Column1.2.2.2.2.1", type text}, {"Column1.2.2.2.2.2", type text}}),    #"Removed Columns2" = Table.RemoveColumns(#"Changed Type5",{"Column1.2.2.2.2.2"}),    #"Split Column by Delimiter6" = Table.SplitColumn(#"Removed Columns2","Column1.2.2.2.2.1",Splitter.SplitTextByDelimiter(" "),{"Column1.2.2.2.2.1.1", "Column1.2.2.2.2.1.2"}),    #"Changed Type6" = Table.TransformColumnTypes(#"Split Column by Delimiter6",{{"Column1.2.2.2.2.1.1", Int64.Type}, {"Column1.2.2.2.2.1.2", type text}}),    #"Removed Columns3" = Table.RemoveColumns(#"Changed Type6",{"Column1.2.2.2.2.1.2"}),    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns3",{{"Column1.2.2.2.2.1.1", "Distance"}}),    #"Split Column by Delimiter7" = Table.SplitColumn(#"Renamed Columns1","Column1.1",Splitter.SplitTextByDelimiter(" "),{"Column1.1.1", "Column1.1.2", "Column1.1.3"}),    #"Changed Type7" = Table.TransformColumnTypes(#"Split Column by Delimiter7",{{"Column1.1.1", type text}, {"Column1.1.2", Int64.Type}, {"Column1.1.3", type text}}),    #"Removed Columns4" = Table.RemoveColumns(#"Changed Type7",{"Column1.1.1", "Column1.1.3"}),    #"Renamed Columns2" = Table.RenameColumns(#"Removed Columns4",{{"Column1.1.2", "Race"}, {"Column1.2.2.1", "StartTime"}, {"Column1.2.2.2.1", "RaceName"}})in    #"Renamed Columns2"
    Not sure where to start with this.



    Taking my C# further

    Sunday, November 1, 2015 9:50 AM

Answers

All replies

  • Hello.

    Its a pity, but due to some url connection problems its almost impossible to understand you query and locate a problem - I can't get source into PQ.

    But on this page I found a link to CSV file, you can see it here .

    May be, a solution? 


    Maxim Zelensky Excel Inside

    Sunday, November 1, 2015 11:27 AM
  • Hi

    Wish that csv was useful, i wrote a python script a while ago, the xml was somewhat more useful.

    Just wanted to see if you could in essence create a function in the import you are doing while your doing it and then record that macro updating the rest of the import and then have it merge the queries with function together.


    Taking my C# further

    Sunday, November 1, 2015 12:07 PM
  • Your script worked OK for me.

    I think your remaining steps are:

    1. Turn your Query into a Function
    2. Get a list of the 18 tables you need (which presumably will include the variable parts of the URL) in a Query
    3. Add a call to the function to that Query, and expand the result

    This blog post has a pretty good detailed walkthrough of the steps:

    https://kzhendev.wordpress.com/2014/04/14/scraping-the-web-with-power-query/

    Monday, November 2, 2015 1:29 AM