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

[theadventurer]

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.

[Ailsa-msft]

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]

@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.

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

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

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.

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.

 

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.