M language , parameters and looping

[skhalatian]

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

[Interkoubess]

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]

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 · Accepted solution]

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..

[skhalatian]

awesome that's just done the trick