Forum Discussion

Seantoh's avatar
Seantoh
Frequent Visitor
8 years ago
Solved

How to get all the table data from a website?

Hi,

 

I follow the step by the tutorial given. But failed.

I need to get the 4 digits data from 0000 until 9999 winning hits table.

Below is my script:

 

let
Source = Web.Page(Web.Contents("http://www.4dking.com.my/num_detail.php?number=0000&Permutations=on&allwm=on&M=on&ST=on&PMP=on")),
Data5 = Source{5}[Data]
in
Data5

 

 

Please teach me how to get the data from number 0000 until 9999. 

Thanks

 

Regards

sean 

 

Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    Hi Seantoh,

     

    I just rechecked the solution, and it still works for me. Just make sure you've renamed the function(step 1) to GetData. :smileyhappy:

     

     

    Regards

7 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi Seantoh,

     

    Based on my test, you should be able to use the M queries below to get the data from number 0000 until 9999. :smileyhappy:

     

    First, create a function to get data for a specific number.

    (n as number) as table=>
let
Source = Web.Page(Web.Contents("http://www.4dking.com.my/num_detail.php?number="&Text.PadStart(Number.ToText(n),4,"0")&"&Permutations=on&allwm=on&M=on&ST=on&PMP=on")),
Data5 = Source{5}[Data]
in
Data5

    Then, iterate over all numbers from number 0000 until 9999 by invoking the GetData fucntion.

    let
    NumberRange = {1..9999},
    Source = List.Transform(NumberRange, each try {_, GetData(_)} otherwise null),
    First = List.FirstN(Source, each _ <> null),
    Table = Table.FromRows(First, {"Number", "Column1"}),
    Expanded = Table.ExpandTableColumn(Table, "Column1", {"4D No.", "4D Prize", "Draw Id", "Date", "Day", "Type", "Gap(days)"}, {"Column1.4D No.", "Column1.4D Prize", "Column1.Draw Id", "Column1.Date", "Column1.Day", "Column1.Type", "Column1.Gap(days)"})
in
    Expanded

     

    Regards

    • Seantoh's avatar
      Seantoh
      Frequent Visitor
      8 years ago

      Hi v-ljerr-msft,

       

      Thank for your reply. Sorry to say that this is the 1st time i use Power Bi. 

      i am done for the 1st step (Create a function to get data for specific number), then pop out Enter Parameter and ask me to invoke it.

      so i type a number and click invoke.

       

      Then, how to iterate over all numbers from number 0000 until 9999 by invoking the GetData function? 

      Can you show me the step? sorry, i am newbie. hehe....

       

      Thank again.

       

      Regards

      sean 

      • v-ljerr-msft's avatar
        v-ljerr-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        8 years ago

        Hi Seantoh,

        Then, how to iterate over all numbers from number 0000 until 9999 by invoking the GetData function? 

        Can you show me the step? sorry, i am newbie. hehe....

        1. Add a Blank Query under New Source.

         

         

        2. Copy and Parse the M query below to Advanced Editor, then click "Done". :smileyhappy:

        let
    NumberRange = {1..9999},
    Source = List.Transform(NumberRange, each try {_, GetData(_)} otherwise null),
    First = List.FirstN(Source, each _ <> null),
    Table = Table.FromRows(First, {"Number", "Column1"}),
    Expanded = Table.ExpandTableColumn(Table, "Column1", {"4D No.", "4D Prize", "Draw Id", "Date", "Day", "Type", "Gap(days)"}, {"Column1.4D No.", "Column1.4D Prize", "Column1.Draw Id", "Column1.Date", "Column1.Day", "Column1.Type", "Column1.Gap(days)"})
in
    Expanded

         

        Regard