Forum Discussion

skhalatian's avatar
skhalatian
Frequent Visitor
8 years ago
Solved

M language , parameters and looping

Hi

I want to be able you get the Market CAP  few market tickers

I have created a parameter called "Ticker" and set the value to "VMW"

 

also create a table  with the following MQuery

 

let
Symbol= Ticker,
Var= Symbol&"?p="&Symbol,
Source = Web.Page(Web.Contents("https://finance.yahoo.com/quote/" & Var)),
Data1 = Source{1}[Data],
#"Changed Type" = Table.TransformColumnTypes(Data1,{{"Column1", type text}, {"Column2", type text}}),

#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column2", Ticker}}),
#"Removed Bottom Rows" = Table.RemoveLastN(#"Renamed Columns",7),
#"Transposed Table" = Table.Transpose(#"Removed Bottom Rows"),
#"Transposed Table1" = Table.Transpose(#"Transposed Table"),
#"Added Custom" = Table.AddColumn(#"Transposed Table1", "Custom", each Ticker),
#"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Custom", "Column1", "Column2"})

in
#"Reordered Columns"

 

This works for one symbol and 

 

however yahoo API only accepts one symbol at the time, so I wanted to change the parameter to list of values as suppose to one value and loop through them and generate the output table

 

Desired output should be something like

 

VMW        Market Cap    46.48

MSFT        Market Cap    378

 

and so on

 

thanks in advance

Show and Tell
Tips and Tricks
  • Interkoubess's avatar
    Interkoubess
    8 years ago

    Hi skhalatian,

     

    Let's say you have these symbols table imported in your power query

    1) Import your symbols table

    2) Create a new column by invoking this function

    3) Expand the tables created

    4) Remove useless columns..

     

4 Replies

  • Interkoubess's avatar
    Interkoubess
    Solution Sage
    8 years ago

    Hi skhalatian,

     

    You can create a function with this code and then create a column with all symbols and apply the function:

     

    let Test=(Var)=>
let
Source = Web.Page(Web.Contents("https://finance.yahoo.com/quote/" & Var)),
Data1 = Source{1}[Data],
#"Changed Type" = Table.TransformColumnTypes(Data1,{{"Column1", type text}, {"Column2", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column2", "VMW"}}),
#"Removed Bottom Rows" = Table.RemoveLastN(#"Renamed Columns",7),
#"Transposed Table" = Table.Transpose(#"Removed Bottom Rows"),
#"Transposed Table1" = Table.Transpose(#"Transposed Table"),
#"Added Custom" = Table.AddColumn(#"Transposed Table1", "Custom", each "VMW"),
#"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Custom", "Column1", "Column2"})
in
#"Reordered Columns"
in 
Test

    Let's say you have a column or ( parameter) with all your symbols then apply this function ( add column).

     

    Hope it helps..

     

    Ninter

    • skhalatian's avatar
      skhalatian
      Frequent Visitor
      8 years ago

      Thanks, that is definitely a step closer to where I want to get

      can you please advise as to how I can use the function progrmaticly to feed let's say 3 symbols and it automatilly generate the table on fly. with out me clicking on invoke button.

       

      the idea is that I have atble with symboles and I want to fetch the market cap for all of those in one table

       

      Thanks again

      • Interkoubess's avatar
        Interkoubess
        Solution Sage
        8 years ago

        Hi skhalatian,

         

        Let's say you have these symbols table imported in your power query

        1) Import your symbols table

        2) Create a new column by invoking this function

        3) Expand the tables created

        4) Remove useless columns..