Excel-Power Query-Blank File Method

Excel-Power Query-Blank File Method

Method 1: Using a Blank File

Step 1: Generate Reports

First, generate the Profit and Loss and Balance Sheet reports by following their respective guides.

Step 2: Access Power Query

  1. Open Excel and go to the Data tab.
  2. Click on Get Data, then select Other Sources.

  3. Choose Blank Query to open the Power Query Editor.

Step 3: Configure Power Query

  1. In the Power Query Editor, go to the Home tab and select Advanced Editor.   
  2. Remove the existing code and replace it with the provided code.

    let

        Token = "Bearer (Paste the Bearer Token Here)",

        Url = "(Profit & Loss API URL)",

        

        GetAPIData = (token as text) as record =>

    let

    Source = Json.Document(Web.Contents(Url, [Headers=[Authorization=token]])),

    AnotherAPICall = if Record.HasFields(Source, {"url"}) then

    Json.Document(Web.Contents(Source[url]))

    else

    Json.Document(Source[data]),

    Result = AnotherAPICall

    in

    Result,

    FinalResult = GetAPIData(Token)

    in

    FinalResult
  3. Replace the placeholder text with your actual Bearer Token and Report API URL and remove Brackets.
       
  4. Click Done.

Step 4: Finalize Data Import

  1. Click on Edit Credentials, select Connect, and set the Privacy level to Public.
  2. Click on Into Table.

  3. Click on the double-sided arrow next to the column header, then select Expand to new rows.
       
  4. Click OK to display the data in Excel.

      
     
 


    • Related Articles

    • Power Bi-Power Query-Blank File Method

      a) Using a Blank File Step 1: Generate Reports First, generate the Profit and Loss and Balance Sheet reports by following the respective guides. Step 2: Access Power Query Open Power BI Desktop. Go to the Home tab and click on Get Data. Select Blank ...

    • Excel Add-in-Blank File Method

      Method 1: Using a Blank File Step 1: Install the Excel Add-in For Single Users: Open Excel Desktop or Excel Web. Navigate to the Home tab. Go to the Add-in section and click on "+More Add-ins". Search for "Accounting.Bi" and install the Accounting Bi ...

    • Power Bi-Custom Connector-Blank File Method

      Method 1: Blank File Step-by-Step Instructions Generate the Report: Follow the respective guides to generate your report. Download the .mez File: Access to the .mez file is provided to paid users. Download the file. Create Necessary Folders: Navigate ...

    • Excel Power Query-Template Method

      Using Templates: Step 1: Access Templates Visit https://accounting.bi/template-dashboards/. Download the desired Excel template. Step 2: Configure Power Query in Template Open the downloaded Excel template. Go to the Data tab and launch the Power ...

    • Power Bi-Power Query-Template Method

      a) Using Templates Step 1: Access Templates Visit the https://accounting.bi/template-dashboards/. Step 2: Connect to Support Contact support at support@accounting.bi to build your template from scratch or to connect your organization's data in the ...