Importing Departments via API to PowerBI

  • 3 April 2020
  • 2 replies

Hi Am pulling in departments using below query:



    Source = Json.Document(Web.Contents(url & "/itil/departments.json")),

    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null),

    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"created_at", "head_id", "id", "name", "prime_user_id", "head_name", "prime_user_name"}, {"department.created_at", "department.head_id", "", "", "department.prime_user_id", "department.head_name", "department.prime_user_name"}),

    #"Filtered Rows" = Table.SelectRows(#"Expanded Column1", each true)


    #"Filtered Rows"

I am only getting 10 records through (the first page of departments in Freshservice) where as I have 14 total. Any ideas where I am going wrong? 

2 replies

Userlevel 2
Badge +4

We have recently launched forum for Freshworks developer community ( We encourage all the developers to discuss queries, ambiguities, know-hows, and points of interest with fellow app developers and Freshworks’ engineers on the forum.  

Visit the forum to sign up and be part of the awesome developer community!

Do you please mind posting the same question in the new Forum? We have moved all our experts to this place where you can expect faster help.

Userlevel 2
Badge +2



As an alternative way, you can import departments in CSV through a third-party app. Then import a CSV file to PowerBI.


Here’s how to import Freshservice departments to CSV.


  1. Connect Freshservice as your Source Platform.

  2. Then pick up CSV as your Target Platform.

  3. Select departments as the data you want to import.

  4. Then click on Continue.

  5. Check if departments migrated to CSV correctly.

  6. Import all departments to CSV.


As soon as you’ve got the CSV files, open up your PowerBi.


  1. Go to Home and click on the Get Data icon.

  2. Then choose CSV from the top of the list on the right.

  3. Click the Connect button.

  4. In the new window, choose the CSV files you want to import.  Press the Open button to load the file.

  5. View the content of your CSV files.


I hope this option can be useful for you. 

In case you need to need more information on CSV import, check this out.