General Ledger Power Bi

0 views

Skip to first unread message

Christain Cobb

unread,

Aug 5, 2024, 11:32:03 AM8/5/24

to telnedalit

Iuse PowerBI desktop to connect to Quickbooks online. I use the General Ledger table to do most of my reporting. However, it looks like the authentication for QBO changed so, I can no longer add tables using the old data sources, or login to different QBO companies. So, I am trying to use the new QBO connection in PowerBI, but when I do the General Ledger Table is not an option...

Please tell me that others are having the same issue and that there is a fix. The General Ledger Table was amazing and is why I was using this connection over just downloading the data through an ETL.

The old connector has the "General Ledger" table, the new one does not. If the customer is trying to connect to QBO using the Get Data button -> QuickBooks Online they will be connecting to the new connector.

Please try accessing the old connector via a Blank Query.

Hi, thnak you for the fix to get into the general ledger, that was very helpful. Once I drill down, I have a different issue where one of the columns won't load related to the date. "DataFormat.Error: We couldn't parse the input provided as a Date value.

Details:

Beginning Balance"

I have pulled the GL data, however, the currency column is missing in the Table and the value is showing in a posted currency, not in converted home currency.

Like if the transaction is posted in USD it's showing USD amount and if posted in SAR it's showing SAR but there is another where we can identify what transaction currency to convert into the single currency.

Me too I have pulled GL 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 - and there is no column to let us know which currency is in USD and which one is in CAD.

Thank you for the helpful tip. I have managed to login to my QuickBooks. However, I have got two entities in the QBO and, ideally, I would like to pull the data from both entities so that I can prepare the group accounts. At the moment, I can only pull information from one of those two entities at a time. Is there any workaround?

Why on earth was the GL table removed in the new QBO connector? This is ludicrous! This table provides access to all of the ley financial data and saved having to conbined a tonne of other tables. Get it back in the new QBO connector, NOW!

"The old connector has the "General Ledger" table, the new one does not. If the customer is trying to connect to QBO using the Get Data button -> QuickBooks Online they will be connecting to the new connector.

Please try accessing the old connector via a Blank Query."

WHY WOULD YOU GO BACKWARDS???? AGAIN!

After I type in QuickBooks.Tables(), I select Data source Settings, Edit permissions, Edit, Sign in as different user (because I need to access a specific client account). Then, I get this instead of a login screen.

I've been struggling with a topic any Dynamics Business Central + Power BI experts would probably laught about. Have googeled for different solutions but I'm not too experienced in Power BI to follow the description.

Can somebody link a simple tutorial or explain by steps how can I separate in Power BI data model Business Central datatable 'Dimension Set Entry' in a way I could use all dimensions separately as I can in Business Central? The need is to create financial data reports (actual costs vs. budget by dimensions) based on Business Central ledgers to be filtered by each dimension separately. I'm running on BC version 19, soon to be 20.

There can be some advantages to doing this as you will only have joins to a dimension where there is a value. If you leave the full table, you will get a join in all cases but a specific dimension may have no value. Does this matter? Sometimes, sometimes not. Key thing to consider is are you going to re-use this in lots of reporting and are you going to add more dimension to BC at a later date. If reusing their approach makes sense. If adding dimensions later, you need to manually add a new table into Power Bi.

The second approach seems to be using a function to split the dimensions into unique tables which seems neat. I will look at that later in more detail. However, he is limiting himself to only shortcut dimensions.

The key to both their approaches is that they have established a consistent methodology as a foundation for all their reporting. If you have a one off small report, you can be quicker keeping things more simple. If you are building a reporting suite and want a solid foundation, a structured approach is always beneficial.

My next and probably the last obstacle is how to create relationship between current 'dimension set id' and general ledger transactions with 'dimension set id'. This is many to many relationship at the moment.

Remove the columns not needed to leave dim set id, the dimension identifier and the dimension value identifier. I have retained The description columns rather than the code columns as I am thinking end user. Under different circumstances, I may keep the codes rather than descriptions

Worth noting that this approach shows all dimensions as columns regardless of what data we join the table too. If you only want relevant dimesnions to show, you'd be best off filtering them earlier in your data extraction so that only required ones appear.

Although innovation can sometimes be a daunting and uncomfortable process for businesses, CEOs must understand emerging technologies and foster innovation, regardless of their industries and level of success. Innovation helps businesses stay ahead of their competitors by reducing costs, improving efficiencies and creating better experiences for their employees.

Recently, EY teams reinvented its general ledger (GL) posting process using the Microsoft Power Platform. As a result, the lead time for the high-frequency, semi-manual process, which integrates with the EY SAP ERP system, was reduced by 95% compared with the legacy process.

At EY, 400 finance professionals across the globe prepare and post data modifications within EY GL. However, EY GL involved time-consuming data transfers between different applications and geographies and was an error-prone process more often.

Emil Hovgaard, EY Nordic Tech Hub Leader, and Ajith Haripaul, EY Global Controlling Transformation Leader, set out to streamline this high-frequency global process with the help of the team at EY Nordic Tech Hub. The team worked together to create a one-stop shop for finance professionals that would improve efficiency with additional functionalities such as real-time dashboards, built-in approval workflows, on-screen validation and enhanced processing speeds.

EY Nordic turned to Microsoft Power Platform, which provides all the required business application capabilities, such as speed to market, low-code customization and configurability, and scalability, in addition to the pre-built connectors, such as the SAP ERP connector. Then, they were able to quickly develop a solution called PowerPost that leveraged the strengths of the combined forces between EY Nordic, Microsoft and SAP.

One of the most critical challenges that the developers faced during the creation of the tool was the need for a gateway configuration to connect the on-premises ERP database of the cloud-based Power Platform solution. PowerPost leveraged the power of Microsoft Data Gateway to address this requirement.

There is a lot more to PowerPost than just posting GL transactions. The implementation flexibility and user-friendly dashboard are attractive benefits for finance departments looking to improve the accuracy, efficiency and speed of the GL posting process in the organization.

The result? The team was able to deploy PowerPost to 250 users across selected global regions to receive overwhelmingly positive feedback. Moreover, EY teams have reduced lead times by 95% through the reinvention of the GL posting process with PowerPost.

At EY, every day is a new opportunity to create something beautiful. Recently, the EY Nordic Tech Hub team transformed their annual salary review process with Microsoft Power Platform. With this new technology, a process that once took four months now takes only two. The solution uses Power Apps, Power Automate, Power BI, and SQL and is connected to SharePoint online. Read more here.

In addition, together with Microsoft Unified support experts, EY Nordics created an enterprise application to automate HR hiring processes and reduce manual administrative tasks. With many such fruitful collaborations, EY and Microsoft teams continue to transform the working world by combining the best of both worlds.

EY refers to the global organization, and may refer to one or more, of the member firms of Ernst & Young Global Limited, each of which is a separate legal entity. Ernst & Young Global Limited, a UK company limited by guarantee, does not provide services to clients.

EY is a global leader in assurance, tax, transaction and advisory services. The insights and quality services we deliver help build trust and confidence in the capital markets and in economies the world over. We develop outstanding leaders who team to deliver on our promises to all of our stakeholders. In so doing, we play a critical role in building a better working world for our people, for our clients and for our communities.