Wayfair to Power BI Integration - Anyone Using API or Automation?

[Amirulfahmi6]

Hi everyone,

We're currently downloading reports manually from Wayfair Partner Home and loading the Excel files into Power BI.

I discovered that Wayfair has a Developer Portal with GraphQL APIs, but I don't currently have API permissions from my admin.

Has anyone successfully connected Wayfair data to Power BI?

I'm interested in:

• Direct Power BI connection to Wayfair APIs
• Power Automate solutions
• Alternative ways to automate report downloads and refreshes
• Best practices for Wayfair reporting

Would appreciate hearing how others have set this up.

Thanks!

[v-abhinavmu]

Hi @Amirulfahmi6,

May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.

Thank you

[Jai-Rathinavel]

@Amirulfahmi6  Please check the below steps 

Use Power Query's Web.Contents to authenticate and pull data directly from Wayfair's GraphQL API.

Step 1: Get OAuth Token

let
    clientId = "YOUR_CLIENT_ID",
    clientSecret = "YOUR_CLIENT_SECRET",
    tokenResponse = Web.Contents(
        "https://sso.auth.wayfair.com/oauth/token",
        [
            Headers = [#"Content-Type" = "application/json"],
            Content = Json.FromValue([
                grant_type = "client_credentials",
                client_id = clientId,
                client_secret = clientSecret
            ])
        ]
    ),
    accessToken = Json.Document(tokenResponse)[access_token]
in
    accessToken

Step 2: Query the GraphQL Endpoint

let
    token = accessToken,
    body = Json.FromValue([
        query = "query getDropshipPurchaseOrders($limit: Int32) { getDropshipPurchaseOrders(limit: $limit) { id poNumber poDate orderId supplierName customerName } }",
        variables = [ limit = 100 ]
    ]),
    response = Web.Contents(
        "https://api.wayfair.com/v1/graphql",
        [
            Headers = [
                #"Content-Type" = "application/json",
                Authorization = "Bearer " & token
            ],
            Content = body
        ]
    ),
    data = Json.Document(response)[data][getDropshipPurchaseOrders],
    tbl = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    expanded = Table.ExpandRecordColumn(tbl, "Column1",
        {"id", "poNumber", "poDate", "orderId", "supplierName", "customerName"})
in
    expanded

Key Notes

• Prerequisite: Admin must grant the "API" role in Partner Home → User Management.
• Token lifespan: Expires in 12 hrs — always fetch fresh, never hardcode.
• Credentials: Store client_id/secret as Power BI Parameters, not inline in code.
• Privacy Firewall: Set data source privacy to "Organizational" (File → Options → Privacy) or you'll hit Formula.Firewall errors combining the two calls.
• Scheduled refresh: May require an On-premises Data Gateway for dynamic header auth in Power BI Service.
• Pagination: Use List.Generate to loop through large result sets.

[v-abhinavmu]

Hi @Amirulfahmi6,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. 

Thank you.

[v-abhinavmu]

Hi @Amirulfahmi6,

Thanks for reaching out to the Microsoft Fabric Community forum.

Based on the official Microsoft documentation, Power Query's Web.Contents function retrieves data from web services. It supports supplying additional HTTP headers, sending POST requests by using the Content option, and configuring request options such as Query and Timeout.

The Create an API for GraphQL in Fabric documentation also explains that after creating an API, you have an active GraphQL API endpoint that is ready to receive requests. When you connect a data source, Fabric automatically generates the GraphQL schema based on the selected data. Once the schema is generated, you can start prototyping GraphQL queries or mutations, and the API editor can be used to test those queries.

For more details, please refer to the below official documentation:

Create and add data to an API for GraphQL - Microsoft Fabric | Microsoft Learn

Web.Contents - PowerQuery M | Microsoft Learn

I hope this helps. Please feel free to reach out if you have any further questions.
Thank you.

[v-csrikanth]

.