[Excel] Dynamic query source url

[Mastakilla]

Ik heb momenteel onderstaande en volledig werkende query om prijzen in mijn excel te updaten van CoinMarketCap;

code:

let
    Source = Json.Document(Web.Contents("https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?convert=btc&symbol=1INCH,ADA,AUTO,AVAX,sBDO,BNB,BTC,BUSD,CAKE,DASH,DOT,ETH,GRT,INJ,KSM,LINK,LIT,LYXE,REN,RFOX,RUNE,SNX,SRM,VET,WAVES,VGX,XMR,YFI,ZEE", [Headers=[#"X-CMC_PRO_API_KEY"="censored-3ab7-44fe-815c-401cb3178da4"]])),
    data = Source[data],
    #"Converted to Table1" = Record.ToTable(data),
    #"Expanded Value" = Table.ExpandRecordColumn(#"Converted to Table1", "Value", {"name", "symbol", "quote"}, {"name.1", "symbol", "quote"}),
    #"Expanded quote" = Table.ExpandRecordColumn(#"Expanded Value", "quote", {"BTC"}, {"BTC"}),
    #"Expanded BTC1" = Table.ExpandRecordColumn(#"Expanded quote", "BTC", {"price", "volume_24h", "percent_change_1h", "percent_change_24h", "percent_change_7d", "percent_change_30d", "percent_change_60d", "percent_change_90d", "market_cap", "last_updated"}, {"price", "volume_24h", "percent_change_1h", "percent_change_24h", "percent_change_7d", "percent_change_30d", "percent_change_60d", "percent_change_90d", "market_cap", "last_updated"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded BTC1",{"Name"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"name.1", "name"}})
in
    #"Renamed Columns"

Ik zou graag de hard coded string "1INCH,ADA,AUTO,AVAX,sBDO,BNB,BTC,BUSD,CAKE,DASH,DOT,ETH,GRT,INJ,KSM,LINK,LIT,LYXE,REN,RFOX,RUNE,SNX,SRM,VET,WAVES,VGX,XMR,YFI,ZEE", in de source url, halen uit cell A1 in de tab met naam 'CMC', zodat de munten die hij ophaalt dynamisch worden en ik niet telkens mijn query moet veranderen als ik een munt toevoeg.

Heeft iemand een idee hoe ik dit kan doen?

[Belindo]

Yep, met een custom functie in Power Query die een cel uitleest. De functie kun je dan weer gebruiken in je URL.

Ik heb een bestand wat hier gebruik van maakt, maar ben op dit moment niet bij de computer in de buurt.

Die hou je van me tegoed. Maar als je al zoekt op 'get cell content in Power Query' moet je al een heel end komen.

[Mastakilla]

https://stackoverflow.com...cells-value-in-powerquery
Heb net bovenstaande uitleg proberen te volgen, maar ik krijg volgende error:

code:

DataSource.Error: Web.Contents failed to get contents from 'https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?convert=btc&symbol=GetValue(ListOfCoins)' (400): Bad Request
Details:
    DataSourceKind=Web
    DataSourcePath=https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest
    Url=https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?convert=btc&symbol=GetValue(ListOfCoins)

Dit is nu mijn power query:

code:

let
    Source = Json.Document(Web.Contents("https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?convert=btc&symbol=GetValue(ListOfCoins)", [Headers=[#"X-CMC_PRO_API_KEY"="censored-3ab7-44fe-815c-401cb3178da4"]])),
    data = Source[data],
    #"Converted to Table1" = Record.ToTable(data),
    #"Expanded Value" = Table.ExpandRecordColumn(#"Converted to Table1", "Value", {"name", "symbol", "quote"}, {"name.1", "symbol", "quote"}),
    #"Expanded quote" = Table.ExpandRecordColumn(#"Expanded Value", "quote", {"BTC"}, {"BTC"}),
    #"Expanded BTC1" = Table.ExpandRecordColumn(#"Expanded quote", "BTC", {"price", "volume_24h", "percent_change_1h", "percent_change_24h", "percent_change_7d", "percent_change_30d", "percent_change_60d", "percent_change_90d", "market_cap", "last_updated"}, {"price", "volume_24h", "percent_change_1h", "percent_change_24h", "percent_change_7d", "percent_change_30d", "percent_change_60d", "percent_change_90d", "market_cap", "last_updated"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded BTC1",{"Name"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"name.1", "name"}})
in
    #"Renamed Columns"

[ Voor 3% gewijzigd door Mastakilla op 07-03-2021 08:25 ]

[Mastakilla]

Ik vermoed dat bovenstaande foutmelding kwam doordat de URL tussen quotes staat. Ik heb nu de volledige URL in een Excel cell zelf samengesteld, zodat ik de volledige URL kan ophalen in mijn power query... Dit lijkt beter te gaan, maar nu zaagt hij over privacy levels

code:

Formula.Firewall: Query 'CMC_BTC_Values' (step 'Source') is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination.

code:

let
    Source = Json.Document(Web.Contents(GetValue("FullURL"), [Headers=[#"X-CMC_PRO_API_KEY"="censored-3ab7-44fe-815c-401cb3178da4"]])),
    data = Source[data],
    #"Converted to Table1" = Record.ToTable(data),
    #"Expanded Value" = Table.ExpandRecordColumn(#"Converted to Table1", "Value", {"name", "symbol", "quote"}, {"name.1", "symbol", "quote"}),
    #"Expanded quote" = Table.ExpandRecordColumn(#"Expanded Value", "quote", {"BTC"}, {"BTC"}),
    #"Expanded BTC1" = Table.ExpandRecordColumn(#"Expanded quote", "BTC", {"price", "volume_24h", "percent_change_1h", "percent_change_24h", "percent_change_7d", "percent_change_30d", "percent_change_60d", "percent_change_90d", "market_cap", "last_updated"}, {"price", "volume_24h", "percent_change_1h", "percent_change_24h", "percent_change_7d", "percent_change_30d", "percent_change_60d", "percent_change_90d", "market_cap", "last_updated"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded BTC1",{"Name"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"name.1", "name"}})
in
    #"Renamed Columns"

[Mastakilla]

Ik denk dat het nu werkt door dat "ignore privacy" checkboxje aan te vinken! Weet niet of er misschien betere / veiligere manieren zijn om bovenstaand probleem op te lossen...

[Belindo]

Ja, die privacy levels zijn soms een beetje rot. Omdat je bij één datasource bijvoorbeeld 'Personal' en bij de andere 'Organizational' hebt gekozen, mogen deze niet samenwerken. Dan kun je óf de boel negeren, óf in een nieuwe Excel opnieuw beginnen.

Fijn dat het is gelukt!

Dit is trouwens de code die ik gebruik (Edit: precies dezelfde als jij op SO hebt gevonden, denk dat ik hem daar ook vandaag heb ). Eerst moet je je cel een Named Range maken door linksboven een naam te typen, óf via de Name Manager toevoegen. De functie heb je dan maar één keer nodig en kun je gebruiken voor verschillende Named Ranges.

code:

(rangeName) => 
    Excel.CurrentWorkbook(){[Name=rangeName]}[Content]{0}[Column1]

Deze roep ik dan aan met bijvoorbeeld:

code:

Source = Table.FromColumns({Lines.FromBinary(File.Contents(getNamedRange("path_to_file") & "\file.log"), null, null, 1252)}),

waar 'path_to_file een named range van een cell is. Vervolgens kun je met & en "" weer nieuwe tekst koppelen. Dit gaat net zoals in Excel: eenFormule & "wat tekst" & nogEenFormule

In jouw voorbeeld zou je:

code:

Source = Json.Document(Web.Contents("https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?convert=btc&symbol=1INCH,ADA,AUTO,AVAX,sBDO,BNB,BTC,BUSD,CAKE,DASH,DOT,ETH,GRT,INJ,KSM,LINK,LIT,LYXE,REN,RFOX,RUNE,SNX,SRM,VET,WAVES,VGX,XMR,YFI,ZEE", [Headers=[#"X-CMC_PRO_API_KEY"="censored-3ab7-44fe-815c-401cb3178da4"]])),

dan moeten veranderen naar

code:

Source = Json.Document(Web.Contents("https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?convert=btc&symbol=" & GetValue("JouwCell") &"", [Headers=[#"X-CMC_PRO_API_KEY"="censored-3ab7-44fe-815c-401cb3178da4"]])),