Microsoft Power BI Tutorial - How to generate great reports with ERPNext

Hello,

I was recently looking for options to generate beautiful reports with ERPNext and found out about Microsoft Power BI Desktop which is free for personal use and very easy to use. The trickyest part was to be able to gather live data from ERPNext. Here is how I was able to do it (using a bunch of tutorials on the Internet) even though it’s not perfect (I will update this post one I find a better way to do it).

1 - Download Microsoft Power BI Desktop:

2 - Use ERPNext Rest API to load your data:

  • In Power BI, click on “Get Data”
  • Choose “Blank Query”
  • Use the “Advanced Editor”

Here is the code I use to get my list of “Sales Invoice”:

Note: To be able to get the data from the Rest API, you need to specify a valid cookie session ID to the API. As of now, I was unable to do it all with Power BI. I will let you know if I find a solution. For now, use Chrome and login into your ERPNext session. Once logged in, press F12 to show Developer Options. Click on the “Application” tab. Double-click on “Cookies” in the left sidebar and click on your ERPNext URL. Copy the value of the “sid” field to the clipboard and paste it after “sid=” in the “Source” url in the following code:

let
Source = Json.Document(Web.Contents(“http://[your erpnext url]/api/resource/Sales Invoice/?fields=[”“name”“, ““customer””, ““posting_date””, ““total””, ““customer_group””, ““territory””]&limit_page_length”, [Headers=[Cookie=“sid=your cookie session information”]])),
data = Source[data],
#“Converted to Table” = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#“Expanded Column1” = Table.ExpandRecordColumn(#“Converted to Table”, “Column1”, {“customer”, “territory”, “name”, “customer_group”, “posting_date”, “total”}, {“customer”, “territory”, “name”, “customer_group”, “posting_date”, “total”}),
#“Reordered Columns” = Table.ReorderColumns(#“Expanded Column1”,{“posting_date”, “name”, “territory”, “customer_group”, “customer”, “total”}),
#“Sorted Rows” = Table.Sort(#“Reordered Columns”,{{“posting_date”, Order.Descending}}),
#“Renamed Columns” = Table.RenameColumns(#“Sorted Rows”,{{“posting_date”, “Date”}, {“customer”, “Client”}, {“customer_group”, “Groupe de clients”}, {“name”, “# Facture”}, {“territory”, “Représentant”}, {“total”, “Montant AT”}}),
#“Changed Type” = Table.TransformColumnTypes(#“Renamed Columns”,{{“Date”, type date}}),
#“Changed Type with Locale” = Table.TransformColumnTypes(#“Changed Type”, {{“Montant AT”, Currency.Type}}, “fr-CA”),
#“Renamed Columns1” = Table.RenameColumns(#“Changed Type with Locale”,{{“Montant AT”, “Total”}})
in
#“Renamed Columns1”

Use and customize this example to get all the data you need. I just found out about Power BI and I love it. It’s very intuitive and easy to use. Let me know if you find a way to capture the cookie from within Power BI or Chrome. I found a way to parse Internet Explorer Cookies but it’s not ideal in my environement.

I hope it will help some of you!

G.

9 Likes

Thanks for sharing.

An open source alternative might be Open source reports and dashboards | MongoDB | MySQL | PosrgreSQL | SQLserver | elasticSearch | Big Query | AWS Redshift

1 Like

Also interesting - Python based Cubes

http://cubes.databrewery.org

2 Likes
4 Likes

@bardbq thanks for this .Do update when you are able to link erpnext successfully to the power BI.It will greatly enhance reporting in ERPnext.Thanks
Fred

2 Likes

Is this mature yet? Would really like to see it take off. I know a rewrite was in the works at a point.

@Chude_Osiegbu, I dont’ know much about the project itself. There have been updates in the last year but not much activity on Github.

I just really like the concept and it has enabled me to solve some complicated reporting requirements. And it’s in Python too.

looks great will try this. I love Microsoft Onedrive

Hello there, any update on this?

I have followed your guide and paste this code with erpnext url & cookie info but I received an ‘Invalid Identifier’ error in Power BI’s advance editor.

[Headers=[Authorization=“token api_key:api_secret”]]

also works.

2 Likes

Will give it a try. Thanks

I use excel and PowerBI alot, I connect it to ERPnext and build robust reports. it’s the best because of the level of flexibility it provides

Imma just plug this here: GitHub - frappe/insights: Free and Open Source Data Analytics Tool for your Frappe Apps

5 Likes

Will it be added in the Frappe Cloud Marketplace? We are using ERPNext cloud and we would like to have access to this!

There is …

2 Likes

Hello, You have solve this identifier error…? because, we face this error and i can’t find their solution. so, if you have solve this error so, please give a solution.