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.