Excel Expression Error

[MBClarke]

Hi all,

I am no expert on power queries and I have search the internet for hours to see if I can figure out my error on its own, but I seem to have all the answers, but no idea how to correct my issue.

I have a spreadsheet that gathers data from a website. This has been working wonderfully since February, but all of a sudden when I open my Excel sheet, I get an expression error, and I don't know what has changed.

Data Source errors.xlsx
	A	B	C	D
1	Currency	Code	Bank Selling.1	Bank Buying.1
2	EURO	EUR	17.1036	16.3249
3	BRITISH POUND	GBP	19.7066	18.7615
4	UNITED STATES DOLLAR	USD	16.3668	15.7171
FNB Rates

I have uploaded my workbook to see the query and assist in what changed or went wrong.

Thanks for all the help.

 

[arunsjain]

Hi

Can you paste your query? look like some column is missing or change in column name.

Go to query editor, click on advance editor and copy query and paste.

Cheers!!

 

[MBClarke]

Hi Arunsfjain,

Thank you for getting back to me. What I find odd is that I have the same query in multiple Excel documents, some going back to February this year and as far as I know I never changed a column in the query or anything like that, but every copy of this document I have gives the same error.

Here is my query:

let
Source = Web.Page(Web.Contents("https://www.fnb.co.za/rates/ForeignExchangeRates.html")),
Data0 = Source{0}[Data],
#"Changed Type" = let
Source = Web.Page(Web.Contents("Foreign Exchange Rates")),
Data0 = Source{0}[Data],
#"Changed Type" = Table.TransformColumnTypes(Data0,{{"", type text}, {"Currency", type text}, {"Code", type text}, {"Bank Selling", type text}, {"Bank Buying", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type","Bank Selling",Splitter.SplitTextByDelimiter("#(tab)", QuoteStyle.Csv),{"Bank Selling.1", "Bank Selling.2", "Bank Selling.3", "Bank Selling.4", "Bank Selling.5", "Bank Selling.6", "Bank Selling.7", "Bank Selling.8", "Bank Selling.9", "Bank Selling.10", "Bank Selling.11", "Bank Selling.12", "Bank Selling.13", "Bank Selling.14", "Bank Selling.15"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Bank Selling.1", type number}, {"Bank Selling.2", type text}, {"Bank Selling.3", type text}, {"Bank Selling.4", type text}, {"Bank Selling.5", type text}, {"Bank Selling.6", type text}, {"Bank Selling.7", type text}, {"Bank Selling.8", type text}, {"Bank Selling.9", type text}, {"Bank Selling.10", type text}, {"Bank Selling.11", type text}, {"Bank Selling.12", type text}, {"Bank Selling.13", type text}, {"Bank Selling.14", type text}, {"Bank Selling.15", type number}}),
#"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"", "Currency", "Code", "Bank Selling.1", "Bank Buying", "Bank Selling.2", "Bank Selling.3", "Bank Selling.4", "Bank Selling.5", "Bank Selling.6", "Bank Selling.7", "Bank Selling.8", "Bank Selling.9", "Bank Selling.10", "Bank Selling.11", "Bank Selling.12", "Bank Selling.13", "Bank Selling.14", "Bank Selling.15"}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Reordered Columns","Bank Buying",Splitter.SplitTextByDelimiter("#(tab)", QuoteStyle.Csv),{"Bank Buying.1", "Bank Buying.2", "Bank Buying.3", "Bank Buying.4", "Bank Buying.5", "Bank Buying.6", "Bank Buying.7", "Bank Buying.8", "Bank Buying.9", "Bank Buying.10", "Bank Buying.11", "Bank Buying.12", "Bank Buying.13", "Bank Buying.14", "Bank Buying.15"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Bank Buying.1", type number}, {"Bank Buying.2", type text}, {"Bank Buying.3", type text}, {"Bank Buying.4", type text}, {"Bank Buying.5", type text}, {"Bank Buying.6", type text}, {"Bank Buying.7", type text}, {"Bank Buying.8", type text}, {"Bank Buying.9", type text}, {"Bank Buying.10", type text}, {"Bank Buying.11", type text}, {"Bank Buying.12", type text}, {"Bank Buying.13", type text}, {"Bank Buying.14", type text}, {"Bank Buying.15", type number}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type2",{"Currency", "Code", "Bank Selling.1", "Bank Buying.1"})
in
#"Removed Other Columns"
in
#"Changed Type"

 

[MBClarke]

Hi all,

Does anyone have an update for me?

Thanks,
Malcolm

 

[arunsjain]

Remove Yellow highlighted "", from query then try. I hope it will help.

 

[arunsjain]

Sorry I am unable to access this website via power query.

 

[MBClarke]

Hi Arunsjain,

That didn't work as it removed the currency fields from the query.

 

[MBClarke]

Hi Arunsjain,

Is it possible to maybe have the query re-written as it was sent to me by someone who I cannot contact anymore? What I am trying to do is link the web page to a query that shows the sell and buy of each currency and then the ability to use that data elsewhere in the document.

Currently, if I only use the link the data returned cannot be used elsewhere as it is not formatted correctly. What I mean is that if USA buying is in cell C3, if I try to create a formula elsewhere and reference C3, I get odd data returned.

Thanks,
Malcolm

 

[Randall]

Is it possible to try and Step In to through the query to see exactly where the error is coming from? That may narrow it down some.

 

[MBClarke]

How do I step through it??