How can I adjust the amount of records imported with Power Query?

[Course]

I have imported a table from https://www.investing.com/crypto/currencies.

The table on the site contains 1621 records, however after importing the table with Power Query it only shows 100 records.

Does anyone know if I can adjust the query so that I can Import all records?

Thank you

 

[macfuller]

I get the same result. Unfortunately I can't read the source HTML well enough to determine what the issue might be.

It might be some sort of API limitation where they deliberately throttle requests. Hopefully someone more expert than I can deliver the solution.

 

[Course]

I get the same result. Unfortunately I can't read the source HTML well enough to determine what the issue might be.

It might be some sort of API limitation where they deliberately throttle requests. Hopefully someone more expert than I can deliver the solution.

Thanks for reply. Im new to Power query so picking it up gradually. It has some good features but it can be awkward to use.

 

[macfuller]

Stick with it. In many cases it's a data problem, not PowerQuery.

Here's a query I use to get FX rates from the Wall Street Journal site (you may need a subscription). It makes it easy to price our goods into other currencies once it's in a table, probably something similar to what you'd like to do.

/*
    Taken from the Wall Street Journal.  Subscription may be required.
    Pct changes where nothing happens show as "unch" in the data, error in the file
    So after changing all values to decimal the query swaps those errors for null values
*/

let
    Source = Web.Page(Web.Contents("http://online.wsj.com/mdc/public/page/2_3021-forex.html")),
    Data0 = Source{0}[Data],
    RenameCols = Table.RenameColumns(Data0,{{"Column1", "Currency"}, {"Column2", "In US$"}, {"Column4", "Pct Chng Yesterday"}, {"Column5", "Pct Chng YTD"}, {"Column6", "Per US$"}}),
    RemoveCols = Table.RemoveColumns(RenameCols,{"Column3", "Column7"}),
    ChangeToDecimal = Table.TransformColumnTypes(RemoveCols,{{"In US$", type number}, {"Pct Chng Yesterday", type number}, {"Pct Chng YTD", type number}, {"Per US$", type number}}),
    RemoveTextRows = Table.RemoveRowsWithErrors(ChangeToDecimal, {"In US$"}),
    ReplacePctChngWithNull = Table.ReplaceErrorValues(RemoveTextRows, {{"Pct Chng Yesterday", null}}),
    ReplacePctChngYTDWithNull = Table.ReplaceErrorValues(ReplacePctChngWithNull, {{"Pct Chng YTD", null}})
in
    ReplacePctChngYTDWithNull

 

[Course]

Stick with it. In many cases it's a data problem, not PowerQuery.

Here's a query I use to get FX rates from the Wall Street Journal site (you may need a subscription). It makes it easy to price our goods into other currencies once it's in a table, probably something similar to what you'd like to do.

/*
    Taken from the Wall Street Journal.  Subscription may be required.
    Pct changes where nothing happens show as "unch" in the data, error in the file
    So after changing all values to decimal the query swaps those errors for null values
*/

let
    Source = Web.Page(Web.Contents("http://online.wsj.com/mdc/public/page/2_3021-forex.html")),
    Data0 = Source{0}[Data],
    RenameCols = Table.RenameColumns(Data0,{{"Column1", "Currency"}, {"Column2", "In US$"}, {"Column4", "Pct Chng Yesterday"}, {"Column5", "Pct Chng YTD"}, {"Column6", "Per US$"}}),
    RemoveCols = Table.RemoveColumns(RenameCols,{"Column3", "Column7"}),
    ChangeToDecimal = Table.TransformColumnTypes(RemoveCols,{{"In US$", type number}, {"Pct Chng Yesterday", type number}, {"Pct Chng YTD", type number}, {"Per US$", type number}}),
    RemoveTextRows = Table.RemoveRowsWithErrors(ChangeToDecimal, {"In US$"}),
    ReplacePctChngWithNull = Table.ReplaceErrorValues(RemoveTextRows, {{"Pct Chng Yesterday", null}}),
    ReplacePctChngYTDWithNull = Table.ReplaceErrorValues(ReplacePctChngWithNull, {{"Pct Chng YTD", null}})
in
    ReplacePctChngYTDWithNull

Thank you, ill see can I alter this code to do what I want. Ill post back here later

 

[Course]

Thank you, ill see can I alter this code to do what I want. Ill post back here later

Ive been trying to alter this code to work with the table on this page https://www.investing.com/crypto/currencies.

Where do i enter the code?

 

[horseyride]

It may not be elegant, but I can just read the currency page into a web query directly in Excel (Data...From Web...) then use the 11 resulting columns as a source in PowerQuery to clean it up. It imports lots of junk, but the actual table is the only piece that has all 11 columns, so its easy to filter out the other rows

 

[macfuller]

Depending on your Excel version choose either the Power Query ribbon or Get & Transform on the Data ribbon. From Other Sources --> Blank Query will open a new query in the query editor. Find the Advanced Editor button and in the window that opens delete the default text and paste the new query text.

 

[Course]

It may not be elegant, but I can just read the currency page into a web query directly in Excel (Data...From Web...) then use the 11 resulting columns as a source in PowerQuery to clean it up. It imports lots of junk, but the actual table is the only piece that has all 11 columns, so its easy to filter out the other rows

Good idea about using the web query, however it still only imports 100 records. I find the web query difficult to use because it keeps giving script errors. Some websites are unusable with it.

@macfuller

=]Depending on your Excel version choose either the Power Query ribbon or Get & Transform on the Data ribbon. From Other Sources --> Blank Query will open a new query in the query editor. Find the Advanced Editor button and in the window that opens delete the default text and paste the new query text.

Thanks, Ill try this.
Hopefully Ill write the new query correctly. Can someone confirm what the query language of Power Query is. Im not familiar with the Let and In commands

 

[Course]

Ive just tried this but it gives a Token Expected error.

let
    Source = Web.Page(Web.Contents("https://www.investing.com/crypto/currencies")),
    Data0 = Source{0}[Data]