> For the complete documentation index, see [llms.txt](https://help.tacinsight.com/fast-weigh-knowledge-base/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://help.tacinsight.com/fast-weigh-knowledge-base/references/api-documentation/graphql-v2-api/reporting-with-graphql/using-graphql-in-powerbi.md).
# Using GraphQL in PowerBI
{% hint style="info" %}
Some of the information about the GraphQL API may have changed since this article was written! \
\
For the most up-to-date information on the GraphQL API, check out our other GraphQL articles in this section!
{% endhint %}
GraphQL can be connected into Power BI to visualize your data. In order to connect it up, you will need to be familiar enough with "M Query" to manipulate the Data Source's "Advanced Editor" to connect to the API
1. Create a new Power BI File and click Get Data.
2. Select a "Blank Query" as your Data Source for now.
* Leave this blank.
3. Add in a new parameter called `x-api-key`, set the "Type" to "Text", and paste your API Key into the "Current Value".
4. Add in PBI parameters for any variables in your GraphQL Query.
* In this example, we have "To" and "From" Date variables that we call "RangeStart" and "RangeEnd" in the PBI Parameters.
5. Your Queries should look something like this:
6. Navigate back to your blank Query that you added at the start.
7. Right-click the name in the Queries list and select "Advanced Editor".
8. Copy and paste the pre-written query below into the Advanced Editor (overwriting what was already there).
* This will automatically use your API key and the date parameters you set with a *pre-written "*Get Ticket" query.
9.
let
//To and From Date PBI parameters, formatted in yyyy-MM-dd format
FromDate = Number.ToText(Date.Year(RangeStart)) &"-"& Text.PadStart(Number.ToText(Date.Month(RangeStart)),2,"0") &"-"& Text.PadStart(Number.ToText(Date.Day(RangeStart)),2,"0"),
ToDate = Number.ToText(Date.Year(RangeEnd))&"-"&Text.PadStart(Number.ToText(Date.Month(RangeEnd)),2,"0")&"-"&Text.PadStart(Number.ToText(Date.Day(RangeEnd)),2,"0"),
//URL should be your GraphQL Endpoint URL
url = "https://fwt.fast-weigh.dev/v1/graphql",
//Header pulls API Key from x-api-key PBI parameter
header =
[#"x-api-key" = #"x-api-key",
#"Content-Type" = "application/json"],
//Body is copied from Insomnia "Fast-Weigh: Copy Telerik Body" plugin
//You will need to surround the body in quotes, and then escape any internal body quotes by using 2 in a row instead of one: ""
//In the variables, you need to add 3 quotes to end the statement, an ampersand to join the parameters, and the PBI parameter name.
//For Example: "" " & FromDate & " ""
RequestBody=
"{
""query"": ""query GetTickets($FromDate: date!, $ToDate: date!) {\n LoadTicket(\n where: {_and: [\n {TicketDate: {_gte: $FromDate}}, \n {TicketDate: {_lte: $ToDate}}\n ]}\n ) \n {\n TicketKey # Unique, auto-assigned key\n TicketNumber\n Void\n NetWeight\n Operator\n InvoiceNum\n BatchNum\n InvoiceHistoryKey\n InvoiceDate\n TicketDate # Only the Date\n TicketDateTime # The Date and the Time in UTC\n UTCOffset # The UTCoffset that needs to be added to the TicketDateTime to get the correct time\n OrderProduct { # The specific version of a Product on an Order\n OrderProductKey # Unique, auto-assigned key\n Description # The pricing description for the specific version of the product\n FreightType\n Product { # The Product used for the OrderProduct\n ProductKey # Unique, auto-assigned key\n ProductID\n ProductDescription\n UnitOfMeasure\n }\n Location {\n LocationKey # Unique, auto-assigned key\n LocationName\n LocationDescription\n }\n Yard {\n YardKey # Unique, auto-assigned key\n YardName\n YardDescription\n }\n }\n Order {\n OrderKey # Unique, auto-assigned key\n OrderNumber\n Description\n DefaultJob\n PONumber\n OpenProductOrder\n Status\n DeliveryLocation\n PayType\n UseHaulZones\n Customer {\n CustomerKey # Unique, auto-assigned key\n CustomerID\n CustomerName\n TermsCode\n Salesperson { # Default Salesperson set on Customer\n Name\n }\n }\n Salesperson { # Actual Salesperson set on the Order\n Name\n }\n }\n Region {\n RegionKey # Unique, auto-assigned key\n RegionName\n RegionDescription\n }\n TaxCode { # The Tax Code set on the Ticket and what is used for Billing\n Code\n Description\n MaterialPercent\n MaterialPercent2\n FreightPercent\n FreightPercent2\n SurchargePercent\n SurchargePercent2\n }\n Truck {\n TruckKey # Unique, auto-assigned key\n TruckID\n Hauler {\n HaulerKey # Unique, auto-assigned key\n HaulerID\n HaulerName\n }\n }\n HaulZone{\n FreightType\n }\n RatesAndAmounts { # The dollar amounts on the Tickets\n MaterialRateOriginal\n FreightRateOriginal\n SurchargeRateOriginal\n MaterialAmountCalculated\n FreightAmountCalculated\n SurchargeAmountCalculated\n MaterialTaxCalculated\n MaterialTax2Calculated\n FreightTaxCalculated\n FreightTax2Calculated\n SurchargeTaxCalculated\n SurchargeTax2Calculated\n HaulerRateOriginal\n HaulerAmountCalculated\n }\n }\n}\n"",
""variables"": {
""FromDate"": """ & FromDate & """,
""ToDate"": """ & ToDate & """
}
}",
//The following 5 steps convert the response to a table that Power BI can expand
webdata = Web.Contents(url, [Headers=header, Content = Text.ToBinary(RequestBody)]),
response = Json.Document(webdata),
data = response[data],
LoadTicket = data[LoadTicket],
ConvertToTable = Table.FromList(LoadTicket, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
//These steps can be done directly in the editor by expanding each column from the header
#"Expanded Column1" = Table.ExpandRecordColumn(ConvertToTable, "Column1", {"TicketKey", "TicketNumber", "Void", "NetWeight", "Operator", "InvoiceNum", "BatchNum", "InvoiceHistoryKey", "InvoiceDate", "TicketDate", "TicketDateTime", "UTCOffset", "OrderProduct", "Order", "Region", "TaxCode", "Truck", "HaulZone", "RatesAndAmounts"}, {"TicketKey", "TicketNumber", "Void", "NetWeight", "Operator", "InvoiceNum", "BatchNum", "InvoiceHistoryKey", "InvoiceDate", "TicketDate", "TicketDateTime", "UTCOffset", "OrderProduct", "Order", "Region", "TaxCode", "Truck", "HaulZone", "RatesAndAmounts"}),
#"Expanded OrderProduct" = Table.ExpandRecordColumn(#"Expanded Column1", "OrderProduct", {"OrderProductKey", "Description", "FreightType", "Product", "Location", "Yard"}, {"OrderProductKey", "Description", "FreightType", "Product", "Location", "Yard"}),
#"Expanded Product" = Table.ExpandRecordColumn(#"Expanded OrderProduct", "Product", {"ProductKey", "ProductID", "ProductDescription", "UnitOfMeasure"}, {"ProductKey", "ProductID", "ProductDescription", "UnitOfMeasure"}),
#"Expanded Location" = Table.ExpandRecordColumn(#"Expanded Product", "Location", {"LocationKey", "LocationName", "LocationDescription"}, {"LocationKey", "LocationName", "LocationDescription"}),
#"Expanded Yard" = Table.ExpandRecordColumn(#"Expanded Location", "Yard", {"YardKey", "YardName", "YardDescription"}, {"YardKey", "YardName", "YardDescription"}),
#"Expanded Order" = Table.ExpandRecordColumn(#"Expanded Yard", "Order", {"OrderKey", "OrderNumber", "Description", "DefaultJob", "PONumber", "OpenProductOrder", "Status", "DeliveryLocation", "PayType", "UseHaulZones", "Customer", "Salesperson"}, {"OrderKey", "OrderNumber", "Description.1", "DefaultJob", "PONumber", "OpenProductOrder", "Status", "DeliveryLocation", "PayType", "UseHaulZones", "Customer", "Salesperson"}),
#"Expanded Customer" = Table.ExpandRecordColumn(#"Expanded Order", "Customer", {"CustomerKey", "CustomerID", "CustomerName", "TermsCode", "Salesperson"}, {"CustomerKey", "CustomerID", "CustomerName", "TermsCode", "Salesperson.1"}),
#"Expanded Region" = Table.ExpandRecordColumn(#"Expanded Customer", "Region", {"RegionKey", "RegionName", "RegionDescription"}, {"RegionKey", "RegionName", "RegionDescription"}),
#"Expanded TaxCode" = Table.ExpandRecordColumn(#"Expanded Region", "TaxCode", {"Code", "Description", "MaterialPercent", "MaterialPercent2", "FreightPercent", "FreightPercent2", "SurchargePercent", "SurchargePercent2"}, {"Code", "Description.2", "MaterialPercent", "MaterialPercent2", "FreightPercent", "FreightPercent2", "SurchargePercent", "SurchargePercent2"}),
#"Expanded Truck" = Table.ExpandRecordColumn(#"Expanded TaxCode", "Truck", {"TruckKey", "TruckID", "Hauler"}, {"TruckKey", "TruckID", "Hauler"}),
#"Expanded Hauler" = Table.ExpandRecordColumn(#"Expanded Truck", "Hauler", {"HaulerKey", "HaulerID", "HaulerName"}, {"HaulerKey", "HaulerID", "HaulerName"}),
#"Expanded RatesAndAmounts" = Table.ExpandRecordColumn(#"Expanded Hauler", "RatesAndAmounts", {"MaterialRateOriginal", "FreightRateOriginal", "SurchargeRateOriginal", "MaterialAmountCalculated", "FreightAmountCalculated", "SurchargeAmountCalculated", "MaterialTaxCalculated", "MaterialTax2Calculated", "FreightTaxCalculated", "FreightTax2Calculated", "SurchargeTaxCalculated", "SurchargeTax2Calculated", "HaulerRateOriginal", "HaulerAmountCalculated"}, {"MaterialRateOriginal", "FreightRateOriginal", "SurchargeRateOriginal", "MaterialAmountCalculated", "FreightAmountCalculated", "SurchargeAmountCalculated", "MaterialTaxCalculated", "MaterialTax2Calculated", "FreightTaxCalculated", "FreightTax2Calculated", "SurchargeTaxCalculated", "SurchargeTax2Calculated", "HaulerRateOriginal", "HaulerAmountCalculated"}),
//These are nullable fields, and they have to be expanded manually. Make sure to rename column names if any are duplicates like the Name columns
#"Expanded Salesperson" = Table.ExpandRecordColumn(#"Expanded RatesAndAmounts", "Salesperson", {"Name"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Salesperson",{{"Name", "OrderSalespersonName"}}),
#"Expanded Salesperson.1" = Table.ExpandRecordColumn(#"Renamed Columns", "Salesperson.1", {"Name"}),
#"Renamed Columns1" = Table.RenameColumns(#"Expanded Salesperson.1",{{"Name", "CustomerSalespersonName"}}),
#"Expanded HaulZone" = Table.ExpandRecordColumn(#"Renamed Columns1", "HaulZone", {"HZFreightType"}),
//After the nullable fields are expanded, make sure to replace the errors with a blank
#"Replaced Errors" = Table.ReplaceErrorValues(#"Expanded HaulZone", {{"CustomerSalespersonName", ""}, {"OrderSalespersonName", ""}, {"HZFreightType", ""}}),
//Make sure to rename any other duplicate headers
#"Renamed Columns2" = Table.RenameColumns(#"Replaced Errors",{{"Description", "PricingDescription"}, {"Description.1", "OrderDescription"}}),
//Use the Detect Type tool to set the header types automatically, if they are not already set
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns2",{{"TicketKey", Int64.Type}, {"TicketNumber", type number}, {"Void", type logical}, {"NetWeight", type number}, {"Operator", type text}, {"InvoiceNum", Int64.Type}, {"BatchNum", type text}, {"InvoiceHistoryKey", Int64.Type}, {"InvoiceDate", type date}, {"TicketDate", type date}, {"TicketDateTime", type datetime}, {"UTCOffset", Int64.Type}, {"OrderProductKey", Int64.Type}, {"PricingDescription", type text}, {"FreightType", type text}, {"ProductKey", Int64.Type}, {"ProductID", type text}, {"ProductDescription", type text}, {"UnitOfMeasure", type text}, {"LocationKey", Int64.Type}, {"LocationName", type text}, {"LocationDescription", type text}, {"YardKey", Int64.Type}, {"YardName", type text}, {"YardDescription", type text}, {"OrderKey", Int64.Type}, {"OrderNumber", Int64.Type}, {"OrderDescription", type text}, {"DefaultJob", type text}, {"PONumber", type text}, {"OpenProductOrder", type logical}, {"Status", type text}, {"DeliveryLocation", type text}, {"PayType", type text}, {"UseHaulZones", type logical}, {"CustomerKey", Int64.Type}, {"CustomerID", type text}, {"CustomerName", type text}, {"TermsCode", Int64.Type}, {"CustomerSalespersonName", type text}, {"OrderSalespersonName", type text}, {"RegionKey", Int64.Type}, {"RegionName", type text}, {"RegionDescription", type text}, {"Code", type text}, {"Description.2", type text}, {"MaterialPercent", type number}, {"MaterialPercent2", type number}, {"FreightPercent", type number}, {"FreightPercent2", type number}, {"SurchargePercent", type number}, {"SurchargePercent2", type number}, {"TruckKey", Int64.Type}, {"TruckID", type text}, {"HaulerKey", Int64.Type}, {"HaulerID", type text}, {"HaulerName", type text}, {"HZFreightType", type any}, {"MaterialRateOriginal", type number}, {"FreightRateOriginal", Int64.Type}, {"SurchargeRateOriginal", Int64.Type}, {"MaterialAmountCalculated", type number}, {"FreightAmountCalculated", type number}, {"SurchargeAmountCalculated", type number}, {"MaterialTaxCalculated", type number}, {"MaterialTax2Calculated", type number}, {"FreightTaxCalculated", type number}, {"FreightTax2Calculated", type number}, {"SurchargeTaxCalculated", type number}, {"SurchargeTax2Calculated", type number}, {"HaulerRateOriginal", Int64.Type}, {"HaulerAmountCalculated", type number}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type","U","Per Unit",Replacer.ReplaceText,{"FreightType"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","L","Per Load",Replacer.ReplaceText,{"FreightType"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","A","Active",Replacer.ReplaceText,{"Status"}),
#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","I","Inactive",Replacer.ReplaceText,{"Status"}),
#"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","C","Closed",Replacer.ReplaceText,{"Status"}),
#"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4","C","Charge Hide",Replacer.ReplaceText,{"PayType"}),
#"Replaced Value6" = Table.ReplaceValue(#"Replaced Value5","D","Credit Card",Replacer.ReplaceText,{"PayType"}),
#"Replaced Value7" = Table.ReplaceValue(#"Replaced Value6","K","Check",Replacer.ReplaceText,{"PayType"}),
#"Replaced Value8" = Table.ReplaceValue(#"Replaced Value7","S","Charge Show",Replacer.ReplaceText,{"PayType"}),
#"Replaced Value9" = Table.ReplaceValue(#"Replaced Value8","X","Cash",Replacer.ReplaceText,{"PayType"})
in
#"Replaced Value9"
10. When prompted to "Specify How To Connect", click "Edit Credentials" and use "Anonymous Access" to connect.
11. With the above M Query as the base, you can replace the GraphQL body with your own, just make sure to escape any quotes in the GraphQL with double quotes as instructed in the M Query comment.
* If you do replace the GraphQL, you will want to remove all of the steps after the "Convert to Table" step and re-create the expand column steps for your own query.
12. Repeat the data setup until you have all of your desired data sources added, and Close & Apply the Power Query Editor.
{% hint style="info" %}
You can use a flattened version of the Query like in the example, or you could repeat these steps for individually queried tables that you want to bring in, and then join the tables via their keys from the Relationships Model page.
{% endhint %}
---
# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.
## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.
Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:
```
GET https://help.tacinsight.com/fast-weigh-knowledge-base/references/api-documentation/graphql-v2-api/reporting-with-graphql/using-graphql-in-powerbi.md?ask=&goal=
```
`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.
Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.