You are Here
Power BI – Choosing Company name in oData connection – Business Central version
Business Central Featured OData Power BI Uncategorized Web-Services

Power BI – Choosing Company name in oData connection – Business Central version

Back in 2017, I wrote a blog post describing how to create a Power BI model across multiple companies.

Power BI – Choosing Company name in oData connection – Peik’s Corner (versionmanager.dk)

This will still work in Dynamics NAV installations. However, in Business Central installations and in the cloud, it is necessary to use the method described in Steven Renders’ blog post:

HOW DO I: CREATE A MULTI-COMPANY POWER BI REPORT WITH THE BUSINESS CENTRAL CONNECTOR?

So here is the same setup as I made it in 2017 once again using the Business Central connectors.

Create three queries:

  • Customers, based on an oData connection published from page 21.
  • CustTrans, based on an oData connection published from page 25.
  • SalesPeople, based on an oData connection published from page 14.

Normally they would be based on the OData connection:

http:[Server]:[Port]/[Instance]/OData/Company(‘CRONUS%20International%20Ltd.’)/Customers

to make a query to a table, but if the company name should be selectable, that is not an option.

Instead, make a query based on the Business Central connector, which demands a secure connection. Therefore, we will use the BC220_NavUser service tier:

And enter the endpoint:

That way, you will get the following choices:

So, open the first company and select the desired table:

Then click OK/Transform Data depending on where the Get Data was initiated.

Here it is showing the data from the first company only. Therefore, the last applied step is deleted, giving us this:

Mark the Company (Name) and the Data columns and remove the remaining:

This will leave these two columns:

Now click the Expand icon and select the Name and Data Columns:

This gives a view of all tables in the companies:

Filter to show only the Customers

Lastly, Expand the Data.Data column:

Now select the fields to include in my dataset. The Company Name column will be added to the query with the name: Name. This will have to be renamed to Company Name. That can be done later.

Then click OK.

The result looks something like this:

Remove the Data.Name Columns and Rename The columns to CompanyName,
Customers.Name, Customers.No, Customers.SalesPerson_Code.

Then add the other two tables by clicking Recent query:

Repeat the process described above. Name the two tables CustTrans and SalesPeople. Include the following fields in each table:

CustTrans:

  • Posting_Date
  • Document_Type
  • Document_No
  • Customer_No
  • Currency_Code
  • Amount
  • Amount_LCY

SalesPeople:

  • Code
  • Name

Next, it is necessary to go through all fields and make sure that the datatypes are correct. Strangely enough the datatypes of the fields are better than if the CustTrans query is address directly, but it still needs to be done. Now only the Date Time fields must be changed to Date.

Now it is necessary to rename all company names from Name to CompanyName in all queries.

Power BI cannot handle linking two queries together with multiple fields, therefore we need to make a small work-around.

We will create a new primary key for each table.

To make a new primary key consisting of both CompanyName and Customers.No and CompanyName and SalesPeople.Name, we need to create one or more extra fields in each query.

Therefore, click the Add Column tab, (If you don’t have an Add Column tab, then you are not in the Power Query editor, and you need to click Transform Data first) and click the Custom Column action and add the fields in each query:

Customer:

  • CustomerKey = [CompanyName] & “-” & [Customers.No]
  • SalesPersonKey = [CompanyName] & “-” & [Customers.SalesPerson_Code”]

CustTrans:

  • CustomerKey = [CompanyName] & “-” & [Customers.Customer_No]

SalesPeople:

  • SalesPersonKey = [CompanyName] & “-” & [SalesPeople.Code]

Now we can link the tables together.

Click the Home tab and then Close & Apply.

Then click the Relationships icon.

Now you will see that, because I named the fields the same, Power Bi automatically found the link between the queries. If you have named the fields differently, you will have to make the links manually.

Then I need to make a filter on the CustTrans query to see only Invoices and Credit Memos:

Now it is possible to make a model:

So, I go to the report area and select a Matrix visualization:

Rows:

  • Customers.Name

Columns:

  • SalesPeople.Name

Value:

  • Amount

And a Slicer to select the CompanyNames.

You can add other visualizations to add more filters and spice it up a little.

It could look like this:

Note that the Amount and Amount_LCY fields are not the most suitable fields for the model since it can include VAT. The best would be to publish a new page as a copy of page 25 (Customer Ledger Entries) with the Sales (LCY) field and use that instead.

Post Views: 1,319
Share
Facebook Twitter Pinterest Linkedin

Related Posts

171 thoughts on “Power BI – Choosing Company name in oData connection – Business Central version”

  2. Alright, betvn1vip, let’s see what you got! Gave it a whirl, and it’s not bad, not bad at all. Good selection. Could use a little something extra, but overall, I’m feeling it. You might dig it too! betvn1vip

  9. Hi! Would you mind if I share your blog with my facebook group? There’s a lot of people that I think would really enjoy your content. Please let me know. Cheers

  10. Thanks for the ideas you write about through this blog. In addition, quite a few young women which become pregnant do not even make an effort to get health insurance because they are concerned they won’t qualify. Although some states right now require that insurers give coverage irrespective of the pre-existing conditions. Charges on these kinds of guaranteed plans are usually bigger, but when thinking about the high cost of medical care it may be the safer way to go to protect your current financial potential.

  11. Thank you for the good writeup. It if truth be told used to be a amusement account it. Glance complicated to more introduced agreeable from you! However, how could we keep in touch?

  13. I’m amazed, I must say. Rarely do I encounter a blog that’s both equally educative and entertaining, and let me tell you, you have hit the nail on the head. The problem is something not enough folks are speaking intelligently about. Now i’m very happy that I found this during my hunt for something regarding this.

  31. I know this if off topic but I’m looking into starting my own blog and was curious what all is needed to get setup? I’m assuming having a blog like yours would cost a pretty penny? I’m not very web savvy so I’m not 100 sure. Any recommendations or advice would be greatly appreciated. Thanks

  32. Sweet blog! I found it while searching on Yahoo News. Do you have any tips on how to get listed in Yahoo News?I’ve been trying for a while but I never seem to get there! Many thanks

  33. Hello There. I found your blog the use of msn. This is a really well written article. I will make sure to bookmark it and come back to read extra of your useful info. Thanks for the post.

  37. Hi there, just became alert to your blog through Google,and found that it is truly informative. I am gonna watch outfor brussels. I’ll be grateful if you continue this in future.Lots of people will be benefited from your writing.Cheers!

  38. Hello there! I could have sworn I’ve been to this blog before but after checking through some of the post I realized it’s new to me.Anyways, I’m definitely glad I found it and I’ll be bookmarking and checking back frequently!

  40. Have you ever considered about including a little bit more than just your articles? I mean, what you say is fundamental and all. However imagine if you added some great images or video clips to give your posts more, “pop”! Your content is excellent but with images and clips, this website could undeniably be one of the best in its field.

  41. Здесь доступны как классические игровые автоматы, так и современные видеослоты.
    Casino Pinco
    Современные технологии обеспечивают стабильность и комфорт.

  43. Do you mind if I quote a few of your articles as long as I provide credit and sources back to your website? My website is in the exact same area of interest as yours and my visitors would definitely benefit from some of the information you provide here.

  45. I’m curious to find out what blog system you have been using? I’m experiencing some small security issues with my latest blog and I’d like to find something more safe. Do you have any suggestions?

  50. Greate article. Keep posting such kind of info on your site. Im really impressed by your site. Hi there, You have done an excellent job. I’ll definitely digg it and individually suggest to my friends. I am sure they will be benefited from this web site.

  51. Hi, Neat post. There is a problem with your site in web explorer, might test this? IE still is the market chief and a large component of other people will leave out your wonderful writing because of this problem.

  53. Thanks for one’s marvelous posting! I truly enjoyed reading it, you are a great author.I will ensure that I bookmark your blog and will often come back in the future.I want to encourage you to definitely continue your great posts, have a nice day!

  54. Have you ever thought about adding a little bit more than just your articles? I mean, what you say is valuable and everything. Nevertheless think about if you added some great pictures or video clips to give your posts more, “pop”! Your content is excellent but with pics and videos, this site could certainly be one of the best in its niche. Terrific blog!

  55. Hiya! Quick question that’s totally off topic. Do you know how to make your site mobile friendly? My site looks weird when browsing from my apple iphone. I’m trying to find a template or plugin that might be able to correct this issue. If you have any suggestions, please share.

  56. Hello there! I could have sworn I’ve been to this blog before but after checking through some of the post I realized it’s new to me.Anyways, I’m definitely glad I found it and I’ll be bookmarking and checking back frequently!

  58. Hello There. I found your blog the use of msn. This is a really well written article. I will make sure to bookmark it and come back to read extra of your useful info. Thanks for the post.

  63. This is the right webpage for anybody who wishes to understand this topic. You realize so much its almost tough to argue with you (not that I really will need to…HaHa). You certainly put a fresh spin on a topic which has been discussed for ages. Excellent stuff, just wonderful! http://www.hairstylesvip.com

  67. Do you mind if I quote a few of your articles as long as I provide credit and sources back to your website? My website is in the exact same area of interest as yours and my visitors would definitely benefit from some of the information you provide here.

  112. Hey there! Would you mind if I share your blog with my myspace group? There’s a lot of people that I think would really enjoy your content. Please let me know. Thanks

  155. In comparison to welcome packages, reload bonuses typically have a lower percentage, averaging around 50% in most cases. It is noteworthy that the rules for each promotion featured are clearly articulated and that all the key conditions are in favour of the Australian player community. Given their popularity, the terms and conditions of these promos, are of considerable importance. It is worth noting that some sites might award earnings as credits, subject to the same wagering conditions as the initial offer that was claimed. Consideration should also be given to factors such as whether the winnings from the spins are added to the initial deposit or provided as a separate perk. In contrast, a 70x requirement would lead to a significantly larger sum of $17,500.
    All of these no deposit bonuses are worth claiming because hey, they’re free, but Slotozen’s offer tops my list because all you need to do to claim it is enable the notifications on the app. Casino Name No Deposit Bonus Wagering Requirements Maximum Winnings Slotozen 20 Free Spins for enabling notifications on the app 40x A$130 Bizzo 10 No Deposit Free Spins for downloading the app 40x A$40 Richard Casino 20 Free Spins for completing the sign-up guide 40x A$35 SlotsGem 10 No Deposit Free Spins for downloading the app 40x A$75 SkyCrown 20 Free Spins for phone and email verification 25x A$40 And yeah, I do realise these are quite literally free offers, but that doesn’t mean I’m willing to spend hours on clearing wagering requirements or playing games that I don’t really like.
    Casino welcome bonuses are only available once (when you first sign up) and have a minimum qualifying deposit. The best bonus online casinos in Australia usually offer a deposit match, sometimes including free spins. A match deposit welcome bonus is the most common promotion gambling sites offer to encourage you to sign up. The benefit is that you’ll always have newly added titles to look forward to while enjoying a seamless experience on PC and playing at top mobile casinos in Australia. Use a payment option that ensures quick, smooth transactions and allows you to withdraw funds quickly. Sites with a reputable licence ensure that the games are fair and that the operator adheres to strict security measures. Our team personally tests and rates each bonus by claiming offers, checking wagering terms, and verifying payout conditions before we recommend it.
    In contrast, table games and jackpot games are often excluded or have a significantly lower weighting, ranging from 50% down to 5% or even less. It is a widely accepted fact that the potential for profit increases with longer periods to meet the playthrough requirements. So, a careful examination of the terms is essential to ensure taking the welcome package is the correct thing to do. The playthough requirements specified in the terms and conditions can vary based on the specific deposit offer. Wagering requirements are a significant factor for promos, and any winnings accrued from them. These methods, as outlined in the terms and conditions, are instructed by the management. Many strategies are available to gaming site marketing teams to ensure that the winnings are segregated from a player’s withdrawable balance.
    If it’s not easily accessible, it is often an indicator that a site can’t be trusted. If you sign up with a casino through our links, we may earn a commission — this does not affect our editorial independence or the integrity of our reviews. We recommend that you always read the full terms and conditions of a bonus on the respective casino’s website before playing. To fund our platform, we earn a commission when you sign up with a casino through our links. Our goal is to provide accurate and up-to-date information so you, as a player, can make informed decisions and find the best casinos to suit your needs. At Gambtopia.com, you’ll find a comprehensive overview of everything worth knowing about online casinos. They define how many times you must bet a bonus before cashing out—crucial for knowing how realistic it is to turn bonus funds into real money.
    Besides the standard welcome offers, new mobile casinos also offer exciting no-deposit bonuses, enhancing your experience. Mobile-exclusive offers are often similar to welcome bonuses (sometimes even more generous), with some app-only deals reaching AUD$500 or more in matched funds. The top mobile casinos don’t just offer on-the-go convenience, but also exclusive online bonuses you can activate straight from your phone. You can typically expect anywhere from AUD$5 to AUD$25 in bonus credits, or up to 50 free spins – without having to deposit any of your own cash.
    The even better news is that SkyCrown’s games paid out over A$8,480,000 in the last few months, with big wins including A$94,000 on Mummyland Treasures. New players are entitled to an A$8,000 welcome pack, the average payout time is just 12 minutes, and you can play for big wins on https://bestpayingcasinosaustralia.blackcoin.co/, live dealer games, and more. SkyCrown was established in 2022, but it’s legit and on the level – and it’s one of Australia’s top payout online casinos. Online pokies are naturally the most popular game category at Ricky Casino, and you can currently choose from over 2,000 different titles. Playing at the best payout online casinos in Australia isn’t just about fun — it’s about maximising your chances of winning.
    The no deposit bonuses I listed here are literally free, just sign up, do something small like verify your email or download the app, and the spins or bonus cash will be transferred to your account. No deposit bonuses are everyone’s favourites, but I don’t think they should be the first thing that draws you to a casino. This bonus page serves as a hub, where you’ll find top lists detailing each top bonus offer, its terms and conditions, and other important details about the specific deal. And I don’t mean just the ones that look good on banners – I signed up, examined the terms, claimed the ones that seem good, and played through them to see if they are actually worth it. Follow this step-by-step guide to sign up and start playing at Ricky Casino or any other top-rated site from our list.

  165. Hello,

    I came across your website and noticed there may be some opportunities to improve your search engine rankings, traffic, and online visibility.

    We provide complete SEO services including keyword research, on-page optimization, backlinks, technical SEO, and ranking improvements using professional strategies.

    I would be happy to share our SEO proposal with detailed plans, activities, pricing, and how GSA-based SEO can support faster results.

    Please let me know if you are interested, and I’ll send price and full details.

    Regards,
    Arpit

  168. Great walkthrough on setting up multi-company Power BI reports with Business Central connectors! The step-by-step approach makes it much easier to follow compared to older OData methods. On a different note, if any of your readers deal with precious metals valuation alongside their financial reporting, https://goldcalculator.pro/ is a handy tool worth bookmarking. Thanks for keeping this updated for BC cloud environments!

  169. Great walkthrough on the multi-company Power BI setup with Business Central connectors! The step of deleting the last applied step to make the company name dynamic is a neat trick that saves a lot of headache. For anyone working with technical tools and references alongside these kinds of integrations, https://engineeringtools.net/ has some useful resources worth bookmarking. Thanks for updating this from the 2017 NAV version!

  170. Great walkthrough on the Business Central connector approach for multi-company Power BI reports! I ran into this exact issue when migrating from NAV to BC cloud and the OData URL method simply stopped working. Deleting the last applied step to make the company name dynamic is a neat trick. I actually found this resource helpful when exploring similar Business Central integration patterns. Thanks for keeping this updated from your 2017 post!

Leave a Reply

Your email address will not be published. Required fields are marked *