Dynamically Web.Contents

[mjhaston]

I have an Excel spreadsheet which I feed from a REST API. I'd like to re-use the same spreadsheet and share it via our OneDrive. Is it possible to use the URL to pass parameters into my Excel and change the data source?

Like https://myonedrive.com/reports-fold...id=9999999999999&parameter1=abc&paramter2=efg



I see in the advanced editor of the data source where it defines everything. Just wondering if I could parse the OneDrive url and grab information out of that to use in my API call?

let
Source = Json.Document(Web.Contents("http://mydatasource.com/jsonfeed.pgm?&parameter1=abc&paramter2=efg")),
data = Source[data],
#"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "items"}}),
#"Expanded items" = Table.ExpandRecordColumn(#"Renamed Columns", "items", {"pgprdc", "pgdesc", "pgpca1", "pgpsno", "pgptpe", "pgpgrp", "pgagrp", "pgpca2", "pgpca3", "pglpco", "pgtlpc", "pgapco", "pgtapc", "pgstco", "price", "freight", "cost", "pgprdcs1", "pgprdcs2", "pgmsup", "pusupp", "puunit", "punppd", "punpp1", "naname", "groupName"}, {"items.pgprdc", "items.pgdesc", "items.pgpca1", "items.pgpsno", "items.pgptpe", "items.pgpgrp", "items.pgagrp", "items.pgpca2", "items.pgpca3", "items.pglpco", "items.pgtlpc", "items.pgapco", "items.pgtapc", "items.pgstco", "items.price", "items.freight", "items.cost", "items.pgprdcs1", "items.pgprdcs2", "items.pgmsup", "items.pusupp", "items.puunit", "items.punppd", "items.punpp1", "items.naname", "items.groupName"})
in
#"Expanded items"