• Home
  • About Us
  • Contact Us
  • Privacy Policy
  • Special Offers
Business Intelligence Info
  • Business Intelligence
    • BI News and Info
    • Big Data
    • Mobile and Cloud
    • Self-Service BI
  • CRM
    • CRM News and Info
    • InfusionSoft
    • Microsoft Dynamics CRM
    • NetSuite
    • OnContact
    • Salesforce
    • Workbooks
  • Data Mining
    • Pentaho
    • Sisense
    • Tableau
    • TIBCO Spotfire
  • Data Warehousing
    • DWH News and Info
    • IBM DB2
    • Microsoft SQL Server
    • Oracle
    • Teradata
  • Predictive Analytics
    • FICO
    • KNIME
    • Mathematica
    • Matlab
    • Minitab
    • RapidMiner
    • Revolution
    • SAP
    • SAS/SPSS
  • Humor

Annual accounts for Microsoft Denmark using #PowerBI and XBRL

May 29, 2016   Self-Service BI
May 26, 2016 / Erik Svensen

Annual accounts for Microsoft Denmark using #PowerBI and XBRL

 Annual accounts for Microsoft Denmark using #PowerBI and XBRL

The Danish Central Business Register -https://datacvr.virk.dk/data/?language=en-gb& contains a lot of data including the possibility to find the annual account for all companies in Denmark.

These are available in two formats – pdf and XBRL format – so for instance for Microsoft Denmark ApS we can find the latest Annual Accounts (financial statement) via this search

https://datacvr.virk.dk/data/visenhed?enhedstype=virksomhed&id=13612870&soeg=microsoft&type=Alle&language=en-gb

 Annual accounts for Microsoft Denmark using #PowerBI and XBRL

As you can see, we can get the document via the link XBRL and get the annual account in XML format like this

 Annual accounts for Microsoft Denmark using #PowerBI and XBRL

You can read more about the XBRL format here – https://www.xbrl.org/

So knowing that we can search for companies using their CVR number and find the latest account let’s try to retrieve the data using Power BI Desktop.

Find the company data

Open the Power BI Desktop and choose get data – and the source is Web

 Annual accounts for Microsoft Denmark using #PowerBI and XBRL

This will give you the HTML document

 Annual accounts for Microsoft Denmark using #PowerBI and XBRL

But we want to find the links to the XBRL accounts so we actually don’t want it opened as an HTML document – so click Edit and in the steps pane click the source to change it to open the document as a Text file

 Annual accounts for Microsoft Denmark using #PowerBI and XBRL

And delete the navigation step after Source.

Now we can find all the rows where XBRL is

 Annual accounts for Microsoft Denmark using #PowerBI and XBRL

This gives us the three rows with information about the link to the accounts in XBRL format

 Annual accounts for Microsoft Denmark using #PowerBI and XBRL

Then trim the column and Split the value by ” – gives us a column 1.8 containing the link to the file.

 Annual accounts for Microsoft Denmark using #PowerBI and XBRL

Now we can delete unwanted columns, add an index and filter by 0 in the index column to get the latest account

 Annual accounts for Microsoft Denmark using #PowerBI and XBRL

Now we have a table with a link to the latest XBRL account for a given company.

Retrieve the account information

To get specific data in the accounts we can use this XBRL link to do a new query retrieving the data

So click on Home tab and New source and select Web again

 Annual accounts for Microsoft Denmark using #PowerBI and XBRL

https://datacvr.virk.dk/data/offentliggorelse?dl_ref=ZG9rdW1lbnRsYWdlcjovLzAzLzQwLzg1LzJkL2ZhL2VjZTItNDViMC1iOTFjLWQzOGUzZTIzZTRhYQ

This gives us the navigator to the XML document

 Annual accounts for Microsoft Denmark using #PowerBI and XBRL

If you are interested in specific elements you can tick those – but click for instance Context and click OK and via the Query Settings click source

 Annual accounts for Microsoft Denmark using #PowerBI and XBRL

Click on table in the node – http://xbrl.dcca.dk/fsa

And you get all the account lines

 Annual accounts for Microsoft Denmark using #PowerBI and XBRL

Then expand the Table column and choose all elements

 Annual accounts for Microsoft Denmark using #PowerBI and XBRL

Now in order to get the current years data you can filter by the contextRef c1

Now after some filtering and removing of duplicate rows – we end up with the lines for the profit and loss as seen here

 Annual accounts for Microsoft Denmark using #PowerBI and XBRL

The values are all positive so in order to handle this – we can pivot the data and do multiplication with -1 and unpivot the columns again

 Annual accounts for Microsoft Denmark using #PowerBI and XBRL

Then we can load this data and visualize the Profit and loss in a waterfall chart

 Annual accounts for Microsoft Denmark using #PowerBI and XBRL

By default the waterfall sorts the data by A-Z which isn’t exactly what we want – luckily there is a workaround for this

Choose Edit Queries and add an Index column to the Context query.

 Annual accounts for Microsoft Denmark using #PowerBI and XBRL

Load the data and select the Attribute in the field pane – and in the Modeling select Sort by Column and choose to sort by Index

 Annual accounts for Microsoft Denmark using #PowerBI and XBRL

And after some formatting etc. we can illustrate the Profit & Loss for Microsoft Denmark like this

 Annual accounts for Microsoft Denmark using #PowerBI and XBRL

Now we can wait a few month to the next Account is done and just click refresh.

Using functions and/or parameters you can make this highly flexible – if this post gets more than 10 likes J – I promise to show you how that can be done.

Hope you liked this – if you want to play around with you can download the pbix here.

Filed under Power BI, Power Query
← Aligning elements in the Power Bi Desktop Designer – Workaround #powerbi

Let’s block ads! (Why?)

Erik Svensen

#powerbi, Accounts, Annual, Denmark, Microsoft, using, XBRL
  • Recent Posts

    • WHEN IDEOLOGY TRUMPS TRUTH
    • New Customer Experience Needs and Commerce Trends for 2021
    • A data transformation problem in SQL and Scala: Dovetailing declarative solutions
    • George Wallace Joins Laverne Cox For Comedy Titled ‘Clean Slate’
    • How Microsoft Azure DevOps and Dynamics 365 CRM Work Together to Improve Service Responsiveness
  • Categories

  • Archives

    • February 2021
    • January 2021
    • December 2020
    • November 2020
    • October 2020
    • September 2020
    • August 2020
    • July 2020
    • June 2020
    • May 2020
    • April 2020
    • March 2020
    • February 2020
    • January 2020
    • December 2019
    • November 2019
    • October 2019
    • September 2019
    • August 2019
    • July 2019
    • June 2019
    • May 2019
    • April 2019
    • March 2019
    • February 2019
    • January 2019
    • December 2018
    • November 2018
    • October 2018
    • September 2018
    • August 2018
    • July 2018
    • June 2018
    • May 2018
    • April 2018
    • March 2018
    • February 2018
    • January 2018
    • December 2017
    • November 2017
    • October 2017
    • September 2017
    • August 2017
    • July 2017
    • June 2017
    • May 2017
    • April 2017
    • March 2017
    • February 2017
    • January 2017
    • December 2016
    • November 2016
    • October 2016
    • September 2016
    • August 2016
    • July 2016
    • June 2016
    • May 2016
    • April 2016
    • March 2016
    • February 2016
    • January 2016
    • December 2015
    • November 2015
    • October 2015
    • September 2015
    • August 2015
    • July 2015
    • June 2015
    • May 2015
    • April 2015
    • March 2015
    • February 2015
    • January 2015
    • December 2014
    • November 2014
© 2021 Business Intelligence Info
Power BI Training | G Com Solutions Limited