none
Advanced editor, if statement multiple tables RRS feed

  • Question

  • Hi,

    i am a noob in power queries. I have a question: I want to pull data from a website which has several tables which, depending on the day, may very in number. 

    For example, i would like to append tables which could be 2 tables or 3 tables, depending on the day, like the following:

    let

        Source = Web.Page(Web.Contents("https://www.planetwin365.it/Sport/OddsAsync.aspx?EventID=7916")),

        Data1 = Source{1}[Data],
        #"Cambio1" = Table.TransformColumnTypes(Data1,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
        #"Reverse1" = Table.ReverseRows(#"Cambio1"),
        #"Promuovi1" = Table.PromoteHeaders(#"Reverse1"),

        Data4 = Source{4}[Data],
        #"Cambio4" = Table.TransformColumnTypes(Data4,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
        #"Reverse4" = Table.ReverseRows(#"Cambio4"),
        #"Promuovi4" = Table.PromoteHeaders(#"Reverse4"),

    /* IF THE FOLLOWING TABLE EXIST */

        Data7 = Source{7}[Data],
        #"Cambio7" = Table.TransformColumnTypes(Data4,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
        #"Reverse7" = Table.ReverseRows(#"Cambio7"),
        #"Promuovi7" = Table.PromoteHeaders(#"Reverse7"),

    /* Then */
        Appended = Table.Combine({#"Promuovi1",#"Promuovi4", #"Promuovi7"})

    /* Else */

        Appended = Table.Combine({#"Promuovi1",#"Promuovi4"})

    in 
         Appended  

    Maybe it's just the wrong way of working with power queries, i really don't know how to handle it.

    Thank you very much for your support. 

        
    • Edited by Fra Catts Wednesday, April 5, 2017 1:52 PM
    Wednesday, April 5, 2017 10:47 AM

Answers

  • My suggestion would be the following solution.

    If you can provide a valid event-ID, I can test it (if so required).

    In order to apply all steps above to each of the tables, you can create a function query, e.g. ExpandData as

    (Data as table) =>
    let
        #"Cambio" = Table.TransformColumnTypes(Data,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
        #"Reverse" = Table.ReverseRows(#"Cambio"),
        #"Promuovi" = Table.PromoteHeaders(#"Reverse")
    in
        #"Promuovi"

    After the first line of your original code, you have a navigation table with the elements on the website.

    This is the place to select the required elements, so you only have the tables you need.

    Next step in the query is to add a column with "Invoke Custom Function" using the function above.

    Now you can expand the new column with nested tables and proceed from there.

    Wednesday, April 5, 2017 2:28 PM