Toon posts:

[Excel] Dynamic query source url

Pagina: 1
Acties:

Vraag


  • Mastakilla
  • Registratie: Februari 2001
  • Laatst online: 24-03 09:57
Ik heb momenteel onderstaande en volledig werkende query om prijzen in mijn excel te updaten van CoinMarketCap;

code:
1
2
3
4
5
6
7
8
9
10
11
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?

Beste antwoord (via Mastakilla op 07-03-2021 13:47)


  • Belindo
  • Registratie: December 2012
  • Laatst online: 12:13

Belindo

▶ ─🔘─────── 15:02

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 :P). 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:
1
2
(rangeName) => 
    Excel.CurrentWorkbook(){[Name=rangeName]}[Content]{0}[Column1]


Deze roep ik dan aan met bijvoorbeeld:
code:
1
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:
1
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:
1
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"]])),

Coding in the cold; <brrrrr />

Alle reacties


Acties:
  • +1Henk 'm!

  • Belindo
  • Registratie: December 2012
  • Laatst online: 12:13

Belindo

▶ ─🔘─────── 15:02

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.

Coding in the cold; <brrrrr />


Acties:
  • 0Henk 'm!

  • Mastakilla
  • Registratie: Februari 2001
  • Laatst online: 24-03 09:57
https://stackoverflow.com...cells-value-in-powerquery
Heb net bovenstaande uitleg proberen te volgen, maar ik krijg volgende error:
code:
1
2
3
4
5
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:
1
2
3
4
5
6
7
8
9
10
11
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]


Acties:
  • 0Henk 'm!

  • Mastakilla
  • Registratie: Februari 2001
  • Laatst online: 24-03 09:57
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:
1
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:
1
2
3
4
5
6
7
8
9
10
11
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"

Acties:
  • 0Henk 'm!

  • Mastakilla
  • Registratie: Februari 2001
  • Laatst online: 24-03 09:57
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...

Acties:
  • Beste antwoord
  • +1Henk 'm!

  • Belindo
  • Registratie: December 2012
  • Laatst online: 12:13

Belindo

▶ ─🔘─────── 15:02

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 :P). 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:
1
2
(rangeName) => 
    Excel.CurrentWorkbook(){[Name=rangeName]}[Content]{0}[Column1]


Deze roep ik dan aan met bijvoorbeeld:
code:
1
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:
1
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:
1
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"]])),

Coding in the cold; <brrrrr />

Pagina: 1


Tweakers maakt gebruik van cookies

Tweakers plaatst functionele en analytische cookies voor het functioneren van de website en het verbeteren van de website-ervaring. Deze cookies zijn noodzakelijk. Om op Tweakers relevantere advertenties te tonen en om ingesloten content van derden te tonen (bijvoorbeeld video's), vragen we je toestemming. Via ingesloten content kunnen derde partijen diensten leveren en verbeteren, bezoekersstatistieken bijhouden, gepersonaliseerde content tonen, gerichte advertenties tonen en gebruikersprofielen opbouwen. Hiervoor worden apparaatgegevens, IP-adres, geolocatie en surfgedrag vastgelegd.

Meer informatie vind je in ons cookiebeleid.

Sluiten

Toestemming beheren

Hieronder kun je per doeleinde of partij toestemming geven of intrekken. Meer informatie vind je in ons cookiebeleid.

Functioneel en analytisch

Deze cookies zijn noodzakelijk voor het functioneren van de website en het verbeteren van de website-ervaring. Klik op het informatie-icoon voor meer informatie. Meer details

janee

    Relevantere advertenties

    Dit beperkt het aantal keer dat dezelfde advertentie getoond wordt (frequency capping) en maakt het mogelijk om binnen Tweakers contextuele advertenties te tonen op basis van pagina's die je hebt bezocht. Meer details

    Tweakers genereert een willekeurige unieke code als identifier. Deze data wordt niet gedeeld met adverteerders of andere derde partijen en je kunt niet buiten Tweakers gevolgd worden. Indien je bent ingelogd, wordt deze identifier gekoppeld aan je account. Indien je niet bent ingelogd, wordt deze identifier gekoppeld aan je sessie die maximaal 4 maanden actief blijft. Je kunt deze toestemming te allen tijde intrekken.

    Ingesloten content van derden

    Deze cookies kunnen door derde partijen geplaatst worden via ingesloten content. Klik op het informatie-icoon voor meer informatie over de verwerkingsdoeleinden. Meer details

    janee