cancel
Showing results for 
Search instead for 
Did you mean: 

Quickbooks online General Ledger Table Amounts columns are showing in multiple currencies

There are two connectors for quicbooks online (beta) - the new one is missing GeneralLedger table while the old connector which is still accessible using QuickBooks.Tables() as Source but it has a major issue:

 

So I have pulled ledger data but some amounts are shown in transaction currency (in my case USD) and not in functional/presentation currency (in my case CAD) - this is wrong as the amounts columns are basically useless in this case (it mixes both currencies) - and there is no column to let us know which currency is in USD and which one is in CAD. Either there needs to be 2 columns that indicates the currency name and conversion rate. Or all amount columns need to be shown in the currency in which reporting is being made.

Status: Needs Info

Hi @theadventurer 

I don't really understand your needs, can you describe your problem and needs in detail by means of a video or screenshot ?

 

Best Regards,
Community Support Team _ Ailsa Tao

Comments
Ailsa-msft
Community Support
Status changed to: Needs Info

Hi @theadventurer 

I don't really understand your needs, can you describe your problem and needs in detail by means of a video or screenshot ?

 

Best Regards,
Community Support Team _ Ailsa Tao

theadventurer
Regular Visitor

@Ailsa-msft Hi Ailsa,

 

Sure I will explain with screenshots below, I will start by showing you quickbooks information first:

 

Quickbooks:

Below is a screenshot of a transaction in quickbooks - June 1 2016, the amount of the transaction in USD is 2.5 and when converted to CAD it is 3.27.

1 - USD transaction CAD Reporting Quickbooks.png

As you can see below, the amount is shown in CAD in quickbooks for the account 0010140

2 - USD transaction converted to CAD in QBs.png

 

And below, you can see when I try to run the trial balance that the amount is in CAD which is correct.

3 - Trial Balance in CAD.png

 

Now I will explain Power BI.

Power BI:

 The below is the query that I ran to fetch generalledger table from power bi. - I have not removed any columns.

 

let
Source = QuickBooks.Tables(),
generalledger = Source{[Key="generalledger"]}[Data],
#"Filtered Rows" = Table.SelectRows(generalledger, each [Date] = #date(2016, 6, 1) or [Date] = #date(2016, 6, 15))
in
#"Filtered Rows"

Below is a list of all the columns.

1 - Powr BI.png

Now I will show you the same transaction in Power BI as I showed you in Quickbooks, see below. You can see that the transaction on June 1, 2016 is showing as a 2.5 (USD) and not as a 3.27 (CAD) while other transactions are shown in CAD.  The issue here is there is no way for me to differentiate between USD amounts and CAD amounts - there is no other column or exchange rate given that can help me differentiate between the currency of amounts.

2 Powr BI.png

Solution:

1 - Just like when running Quickbooks trial balance or general ledger report - all amounts are shown in functional currency (in my case CAD) - similarly the generalledger table should just include functional (CAD) amounts that is used for reporting.

2 - The other option is to include 2 new columns in the existing table - exchange rate and currency - that way one can easily convert and get the required functional currency amounts.