How to Connect GraphQL APIs as a Data Source in Power BI
- Vojtěch Šíma
- Aug 3
- 20 min read
Updated: Aug 4
tl;dr GraphQL APIs are just another way apps can talk to each other and fetch some tasty data. Unlike REST (for example), where you ping different endpoints, GraphQL lets you send a single query string saying exactly what you want, and the server figures out how to get it. I’ll walk you through the key GraphQL concepts like variables, aliases, fragments, directives, and more. This intro is all about consuming GraphQL and plugging it inside Power BI using a bit of our trusty M sauce.
As an examples, I'll use https://countries.trevorblades.com/; https://rickandmortyapi.com/graphql
Table of Contents:
Why should you know GraphQL?
GraphQL is a relatively new type of web API. “Relatively” means it has been around for about 10 years since its initial release, but only a few years as a stable version. The primary benefit over the more common REST API is that it reduces the number of requests to different endpoints (on your end). In GraphQL, you don’t call specific endpoints. You send one query where you define what you need, and you get exactly that (quoting the official GraphQL site).
With GraphQL, you don’t need to learn a bunch of separate endpoints. You just learn the structure of the data and query exactly what you want. The host does the heavy lifting.
Because yeah, the "QL" in GraphQL stands for Query Language. I probably should’ve started with that.
Hit me with it
For those who don’t read (me included) and just scan for anchor points, let me hit you right away with an example to satisfy those dopamine receptors.
btw I am not really fan of Star Wars (pls don't stone me over it), it's just an example from official site
In the example, you can see how you build a query by declaring fields and then fields within those fields. It might remind you of JSON, and that’s valid. The structure feels similar. You’ll notice more of these overlaps as we go, but just keep in mind that GraphQL is its own language with its own rules and fluff. The similarities can help you get started, but they’re not the same thing. Still, if thinking of JSON makes it more familiar, then sure, my guy.
Quick & Dirty in M
If you're here just to copy some snippet, I'll satisfy your needs first. But I recommend then staying for the whole picture.
In M, you’ll use Web.Contents() to send your GraphQL query. The actual query goes inside the Content option. Most of the setup is similar to working with a REST API, just keep in mind you’re sending a POST request. As I said, authentication works pretty much the same (check my other blog post if you want the deep dive).
Using the sample API I mentioned earlier, here’s how you’d get a list of countries in Europe with no Authentication needed:
let
query = "
{
continent(code: ""EU"") {
name
countries {
code
name
}
}
}",
requestBody = Json.FromValue([query = query]),
response = Web.Contents(
"https://countries.trevorblades.com/",
[
Headers = [#"Content-Type" = "application/json"],
Content = requestBody
]
),
parsed = Json.Document(response)
in
parsed If you want a table as a result, add this:
let
/* the stuff before parsed */
parsed = Json.Document(response),
// transform data into Table
getTableFancy =
#table(
type table [
continentName = text,
countryName = text,
countryCode = text
],
let
continent = parsed[data]?[continent]?[name]?,
countries = parsed[data]?[continent]?[countries]?
in
List.Transform(
countries,
each {
continent,
[name]?,
[code]?
}
)
)
in
getTableFancyAnd that’s pretty much it for starters. Now I’m gonna explain more GraphQL concepts, definitely worth checking out.
GraphQL concepts & M Implementation
Basic Query Structure
Countries example
A GraphQL query uses curly braces {} to define which fields you want to retrieve. At a minimum, you need to declare at least one root field. Each field can then include subfields, and if you want to return multiple fields or subfields, you just stack them underneath with no commas, no semicolons, no weird punctuation. You don’t even have to format it nicely. GraphQL ignores whitespace, so technically, you can write the whole thing in one messy line if that’s how your soul wants to express itself.
{ continent(code: ""EU"") { name countries{ code name } } }This will work, but it's not that nice, is it?
One thing to watch out for: if you include a field that expects subfields but don’t actually specify them, your query will blow up. Some GraphQL explorers (often built into the API documentation) might make it look like everything's fine by auto-fixing your input. For example, you select the field inputFields, and it quietly adds related fields for you. But that’s just the editor being helpful. The real GraphQL server will still throw an error if you don’t explicitly ask for the required subfields.
If you want your queries to look a bit more standardized, you can (or perhaps should) also start your query with the keyword query, which is optional unless you’re working with more advanced stuff. GraphQL supports three operation types:
query for getting data (what we’ll focus on here)
mutation for writing or changing data
subscription for real-time updates
We won’t be exploring mutations or subscriptions in this article, but it's worth knowing they exist.
Another small tip: you can name your query. It’s optional, but helpful for debugging or reuse.
query GetEuropeanCountries {
continent(code: "EU") {
name
countries {
code
name
}
}
}Field Arguments
Generic example
You probably noticed in the previous example that I didn’t just use curly braces, but also parentheses ( ). Just like in M, that’s where you pass arguments, and the same idea applies here.
If a field requires or supports arguments, you write the field name with parentheses and include one or more key-value pairs inside.
Arguments are written as key: value, where the key is unquoted and the value is quoted only if it’s a string. By default, GraphQL supports scalar types like Int, Float, String, Boolean, and ID. But many APIs define custom scalars or enums, depending on their needs.
Enums are special types with a fixed set of allowed values, like ACTIVE or INACTIVE. They’re not strings, so don’t wrap them in quotes. Just write them as-is, based on what the API expects.
Arguments are handy for filtering, selecting specific objects (such as by ID), or even transforming the result. For example, some fields accept formatting options or date filters as arguments.
Argument values can be scalars, enums, or input objects. If a field argument is required, the schema will mark it with an exclamation mark "!". When writing a query, strings go in quotes, numbers and booleans do not, enum values are unquoted, and object-type arguments use {} with key-value pairs.
Here’s an example of what that looks like (random, not country-related):
accounts(filter: { status: ACTIVE, minBalance: 1000 })Introspection Queries
Rick & Morty example
In order to know how we can filter things or what enums we can use, we need to understand the schema we're working with. Sure, you can scroll through the docs, but if you're more into hands-on exploring, GraphQL lets you query the metadata directly.
So let’s say I’m doing a little "character research" and I wanna know what’s actually in the API. I start with a query using __schema, which tells me what entry points I’ve got. It lists all available queries, so I know what I can ask the server. Think of it as the top-level map of what's possible.
Then I use __type(name: "Characters"), which zooms in on the specific type that the characters query returns. That tells me what fields it exposes, like results and info, and what those fields contain. After that, I drill into the Character type itself to see what a single character object includes: things like id, name, status, and species.
Once I know what’s available and how the data is shaped, I run the actual characters query to get some real data. And here’s where you need to be careful. The query field is called characters with a lowercase c, the type it returns is Characters with a capital C, and the objects inside are of type Character.
Therefore, if you ask about the type, you use capital C as Characters, but when you wanna include the field in your actual query, you type it with lowercase c as characters. It's easy to mix those up and hit errors, especially when switching between introspection and actual queries.
# Step 1: See what operations the API offers
query GetRootOperations {
__schema {
queryType {
fields {
name
description
type {
name
kind
}
}
}
}
}
# Step 2: Explore the structure of the Characters return type
query GetCharactersReturnType {
__type(name: "Characters") {
name
kind
fields {
name
type {
kind
name
ofType {
name
kind
}
}
}
}
}
# Step 3: Check what a single Character object includes
query GetCharacterFields {
__type(name: "Character") {
name
fields {
name
type {
kind
name
}
}
}
}
# Step 4: Fetch actual character data
query GetAllCharacters {
characters {
results {
id
name
species
status
}
}
}
Inline Fragments
Generic example
When querying a field that returns an interface or a union type, you're dealing with a case where the returned objects can be different types, and each type might need different subfields. You can use inline fragments to define which fields you want to query for which type.
For example, let’s say you have a search query that checks a person’s media presence and returns types like Blog, Video, or Photo. Each of those types has different fields. Some fields can be shared, like title, if they all implement the same interface, but the rest are specific to each type, like wordCount for blogs, duration for videos, or size for photos.
It can look something like this:
{
search(text: "graphql") {
results {
title
... on Blog {
wordCount
}
... on Video {
duration
}
... on Photo {
size
}
}
}
}Fragments
Rick & Morty example
If you have a query that returns objects with shared fields and you don’t feel like repeating yourself, fragments have your back. You can define a fragment that lists the fields you always want to include, and then just call that fragment instead of typing out the fields every time.
It behaves exactly as if you typed all the fields manually, but way cleaner.
This is super handy when you’re working with repeated structures, shared object types, or you’re building a bigger query where the same set of fields shows up in multiple places.
Fragments are defined outside the main query using the fragment keyword, followed by a name, and the type the fragment it applies to. You can specify them above your query or below.
query WithFragment {
characters(page: 1) {
results {
...CharacterDetails
}
}
episodes(page: 1) {
results {
name
episode
characters {
...CharacterDetails
}
}
}
}
fragment CharacterDetails on Character {
id
name
status
species
}Variables
Rick & Morty example
One of the key concepts of GraphQL is the use of variables. We use them to avoid hardcoding values directly into the query and to make the whole thing dynamic and reusable.
In M, you’ll often have a list of values that you want to pass into the query. You could technically build the query as a string and concatenate values directly into it, but a cleaner approach is to use a proper GraphQL variable, which means writing a general query that accepts variable input and passing the values separately in a structured way.
Variables are defined in a separate structure. You build a variable dictionary (usually as JSON) using variableName: value format.
Inside the query itself, you replace the static value with a dollar sign and the variable name, like $characterId. Additionally, if you're using variables, you have to declare them at the top of the query like this (name and accpeted type):
query ($characterId: ID!) {
character(id: $characterId) { ... }
}You can also give your query a name (like query getCharacter(...)), which is optional but recommended if you're debugging or running multiple operations.
You can use variables in filters, arguments, pagination, or even combined with fragments. Anywhere you'd normally hardcode something, you can swap it out for a variable.
Example
For this example, we’re calling characters and their episodes. Let’s say you already have another query that gives you a list of character IDs, and for each one, you want to find out which episodes they appeared in.
We’ll start by simulating a case where the API only accepts one ID per request. That means looping through the list and calling each character individually using a variable.
Then I’ll show you how to do the same thing with a single call, which works with the Rick & Morty API since it supports passing a list of IDs using the charactersByIds field.
query GetCharacter($charId: ID!) {
character(id: $charId) {
id
name
episode {
name
episode
}
}
With variables defined like this:
{
"charId": 1
}In M, we treat variables as a separate field in the requestRecord, and inside that, we define them using a standard key: value structure. If you have multiple IDs but your API only supports one ID per request, you’ll need to handle the looping outside the GraphQL itself. That means building the query once and looping through your list of IDs in M. Here's an example of how that looks:
let
graphQLQuery = "
query GetCharacter($charId: ID!) {
character(id: $charId) {
id
name
episode {
name
episode
}
}
}",
listOfIds = {1,7,10},
requestFunction = (query as text, id as number)=>
let
requestRecord =
[
query = query,
variables = [charId = id]
],
requestBody = Json.FromValue(requestRecord),
response =
Json.Document(
Web.Contents(
"https://rickandmortyapi.com",
[
RelativePath= "graphql",
Headers = [#"Content-Type" = "application/json"],
Content = requestBody
]
)
)
in
response,
callCharacters =
List.Transform(
listOfIds,
(i)=> requestFunction(graphQLQuery, i)
)
in
callCharactersIf your API supports passing a list of values, then you don't need to loop at all. You just pass a list into the variable like this.
Instead of:
query GetCharacter($charId: ID!) {
character(id: $charId) { # the rest of codewith variables as:
{ "charId": 1 }You'd write:
query GetCharacters($ids: [ID!]!) {
charactersByIds(ids: $ids) { # the rest of codewith variables as:
{ "ids": [1, 7, 10] }In M, the whole thing looks like this:
let
graphQLQuery = "
query GetCharacters($ids: [ID!]!) {
charactersByIds(ids: $ids) {
id
name
episode {
name
episode
}
}
}",
listOfIds = {1,7,10},
requestFunction = (query as text, id as list)=>
let
requestRecord =
[
query = query,
variables = [ids = id]
],
requestBody = Json.FromValue(requestRecord),
response =
Json.Document(
Web.Contents(
"https://rickandmortyapi.com",
[
RelativePath= "graphql",
Headers = [#"Content-Type" = "application/json"],
Content = requestBody
]
)
)
in
response,
callCharacters = requestFunction(graphQLQuery, listOfIds)
in
callCharactersAliases
Rick & Morty example
If you’ve used SQL before, you know that when you want to return the same column multiple times, aliases come in handy. Sometimes they’re even necessary. GraphQL works the same way: when you need to call the same field more than once but with different arguments, aliases are your friend.
Instead of just writing the field name, you prefix it with your custom alias, followed by a colon, and then the actual field (plus any arguments you want to pass).
Let’s say we want to fetch two types of characters: alive and dead (spoiler alert). We’re calling the same characters field twice, but with different filters. Since we’re repeating the same structure, we can even throw in some fragments to make it fancier and easier to manage.
query {
aliveChars: characters(filter: { status: "Alive" }) {
results {
...CharacterBasics
}
}
deadChars: characters(filter: { status: "Dead" }) {
results {
...CharacterBasics
}
}
}
fragment CharacterBasics on Character {
id
name
status
}
Filtering
Rick & Morty and Countries example
If you want to narrow down results, you’ll use filters. You've probably seen me sneak them into earlier queries, but I haven't properly explained what they are or how they work.
Filters, unlike direct field arguments (like continent(code: "EU")), let you define multiple conditions at once. It’s a simple concept, but super useful whenever you want to limit the amount of data you're pulling from an API.
How to Know What You Can Filter On
Not every field supports filtering. To figure out which ones do, you can use introspection queries to check the root-level fields and their arguments.
Here’s a basic query to get started:
{
__type(name: "Query") {
fields {
name
args {
name
type {
name
kind
}
}
}
}
}
You’re looking for fields that accept a filter argument. If you spot one, like this:
...you’re on the right track.
Now, dig deeper into that input type to see what you can filter by:
{
__type(name: "FilterCharacter") {
inputFields {
name
type {
name
kind
}
}
}
}This gives you a list of all fields you’re allowed to use in the filter, along with their data types.
Once you know the structure, you can run a filtered query like this:
query filteredCharacters {
characters(filter: { status: "alive", species: "human" }) {
results {
species
name
}
}
}What About Operators Like eq or in?
By default, GraphQL filters are pretty simple. They just use colons to assign values. If the API supports advanced string operators like eq, in, contains, or startsWith, those have to be defined in the schema by the developer.
For example, the Countries API example defines a type called StringQueryOperatorInput, which supports exactly that kind of filtering.
You can find it using introspection just like before, and it lets you write queries like this:
{
countries(filter: { continent: { eq: "EU" }, code: { in: ["FR", "DE", "PL"] } }) {
name
code
continent {
name
}
}
}
Directives
Rick & Morty example
Directives allow you to specify, using two keywords, whether you want to include or skip certain structures or fields. In building reports, this option isn't really that useful, because you don’t usually want to change the report structure on the fly as that could easily break the whole thing.
There are two built-in directive types:
@include
@skip
They both use the keyword if:, followed by some expression that returns true or false. Or you can just feed it a true/false value directly.
I did think of a couple of use cases, though, mainly for debugging or initial exploration.
Let’s go with the exploration one. We can build a query that returns the fields we want in the final report, and based on a true/false switch, we can optionally include the structure of the object as well. This is useful when you want to see available fields without constantly checking the documentation. So during exploration, you keep it true, and once you're done, you turn it off.
query ExploreCharacter($id: ID!, $withSchema: Boolean!) {
character(id: $id) {
id
name
status
}
__type(name: "Character") @include(if: $withSchema) {
name
fields {
name
type {
name
kind
ofType {
name
kind
}
}
}
}
}The implementation in M is practically the same as in the example with variables. You store the true/false value as a variable and then build your GraphQL call as a function with that true/false parameter.
Pagination
Rick & Morty example
Last but definitely not least - pagination. Just like with REST APIs, pagination plays a big role in splitting results into smaller chunks called pages. The exact type of pagination depends on the API. It can be cursor-based, offset-based, or something custom.
The implementation in M is practically the same as with the REST API example I mentioned earlier on the blog. In the Rick & Morty API, we get a pretty straightforward setup using a next field. Once next is null, you know there are no more pages to fetch. This API also includes the total number of pages inside the field pages, so technically, you could just generate a list from 1 to 42 (taken from the pages field) and go from there.
If you want a breakdown of the major types of pagination used in REST APIs, check out my blog post here.
The first one might be a bit trickier to implement, so I’ll show you that one.
Given this is our GraphQL query:
query getCharactersWithPaging($pageNumber: Int) {
characters(page: $pageNumber) {
info {
next
pages
}
results {
id
name
status
}
}
}We’ll keep calling this query with the variable pageNumber, on each iteration, we will fetch a new value from the field next. Once the next field returns null, we know we’ve reached the last page and can stop.
You might think about using a directive to include the info field only for debugging and then dropping it later. That sounds nice, but it would actually break the loop. Once you remove the very field you're using to check whether to continue, the whole thing stops working. So yeah, watch out for that.
let
graphQLQuery = "
query getCharactersWithPaging($pageNumber: Int) {
characters(page: $pageNumber) {
info {
next
pages
}
results {
id
name
status
}
}
}",
requestFunction = (query as text, page as nullable number) =>
let
requestRecord = [
query = query,
variables = [ pageNumber = page ]
],
requestBody = Json.FromValue(requestRecord),
response =
Json.Document(
Web.Contents(
"https://rickandmortyapi.com",
[
RelativePath = "graphql",
Headers = [ #"Content-Type" = "application/json" ],
Content = requestBody
]
)
)
in
response,
callCharacters =
List.Generate(
() => [
request = requestFunction(graphQLQuery, null),
nextPage = request[data]?[characters]?[info]?[next]?
],
each [nextPage]? <> null,
each [
request = requestFunction(graphQLQuery, nextPage),
nextPage = [request]?[data]?[characters]?[info]?[next]?
],
each [request]?[data]?[characters]?[results]?
),
combineResults = List.Combine(callCharacters),
buildTable =
#table(
type table [
id = Int64.Type,
name = Text.Type,
status = Text.Type
],
List.Transform(
combineResults,
each {
Number.From([id]?),
[name]?,
[status]?
}
)
)
in
buildTableThere’s nothing sneaky or difficult here; it works just like the nextPage example in my other post. Because our List.Generate is always one iteration behind; we can spot when nextPage turns null and still grab that final page’s results. The only tweak to consider is the starting value: this API accepts 0, 1, or null as the initial page.
Based on the prev field when calling page 2, it suggest 1.
As a result, we end up with a list of lists. We then combine them into a single list of records and use the #table constructor to build a table with explicit types. It would be great to have an automatic constructor too, since the query already defines the types for us.
Experimenting with Auto-Building Typed Tables from GraphQL
Rick & Morty example
Disclaimer: This part shows how to auto-build typed tables straight from a GraphQL API using just a field list. The example is simplified and tailored to the Rick & Morty API, but the core idea still holds. When building any kind of automatic schema generator, consider whether it actually makes sense for your use case. If your schema is complex or requires tons of special conditions, the overhead might not be worth it.
One of the biggest perks of using GraphQL is that we define the schema upfront, so we know exactly what we’re gonna get. And since we know what we’ll get, we can also automatically retrieve the types for the retrieved fields.
If we’re working with a relatively simple schema using primitive types, all we need to do is define the fields we want and pass them into a function that returns an explicitly typed table.
I’ll show you a very basic example using only primitive types and how you might make use of that. Just a heads-up: I’m taking a couple of shortcuts here. I’m only working with scalar fields, and I won’t be performing any renaming or transformation. I’ll take the fields as they are. If you need to reshape or rename anything, you’ll have to make a few tweaks in the #table constructor logic.
Automated Type Record in M
If we want to build a table using the #table constructor with explicitly defined types, we need to dynamically create the part that handles column types. Since our source is GraphQL, and GraphQL supports introspection queries to retrieve type info, we’re gonna take full advantage of that.
First, we define what we want to extract. In this example, I’m using the type "Character". From that, we’ll grab the id, name, and status fields and fetch their types. Once we’ve got those, we need to translate the GraphQL types into M types; they're not a 1:1 match. I’ll include a translation record in the function to handle that mapping. Then we filter just the fields we care about and build the Type Record. If we want to plug that into #table, the type record needs specific properties: Name, Type, and optionally Optional. But since we want all fields to be required, we can safely ignore Optional for now respectively, we will just make all fields mandatory.
let
getTypeSchema = (typeName as text, filterFields as list) =>
let
schemaQuery = "
query getSchema($typeName: String!) {
__type(name: $typeName) {
name
fields {
name
type {
name
kind
ofType {
name
kind
}
}
}
}
}",
requestRecord = [
query = schemaQuery,
variables = [ typeName = typeName ]
],
requestBody = Json.FromValue(requestRecord),
response =
Json.Document(
Web.Contents(
"https://rickandmortyapi.com",
[
RelativePath = "graphql",
Headers = [ #"Content-Type" = "application/json" ],
Content = requestBody
]
)
),
typeTranslation = [
Int = Int64.Type,
Float = type number,
String = type text,
ID = type text,
Boolean = type logical
],
getToFieldsAndFilter =
List.Select(
response[data]?[__type]?[fields]?,
each List.Contains(filterFields, _[name])
),
buildTypeList =
List.Transform(
getToFieldsAndFilter,
each [
Name = [name],
Type = Record.FieldOrDefault(typeTranslation, [type]?[name], type any),
Optional = false
]
),
buildTypeRecord =
Type.ForRecord(
Record.FromList(buildTypeList, filterFields),
false
)
in
buildTypeRecord
in
getTypeSchemaSteps relevant to dynamic type creation:
Define a typeTranslation record mapping GraphQL scalar names (Int, String, etc.) to M types (Int64.Type, type text, etc.).
After fetching response[data][__type][fields], filter to desired fields via List.Select.
For each field, use Record.FieldOrDefault(typeTranslation, fieldTypeName, type any) to resolve its M type (defaulting to type any).
Assemble these into a list of records with Name, Type, and Optional = false.
Call Record.FromList and Type.ForRecord on that list to produce the final, strongly typed M record.
Just keep in mind this isn’t fully universal. If you’re dealing with non-primitive types like objects, lists, or custom scalars, you’ll need extra logic to unwrap and handle those. But the core idea, pulling types via introspection and mapping them to M types, still holds and can be extended as needed.
Build an automatic, explicitly typed table in M
Now we just call our function, pass in the list of fields we want, and build the table. At this point, we can reuse the paginated query from earlier. The only manual step is defining which fields we want from the Character type (static in this example). Once that’s set, we pass the field list to both the schema function and the actual query function, and that's it.
The last step is to rebuild the #table constructor so we’re not manually defining the schema and types anymore. Instead, we use our function to generate both the column types and the rows.
Just note: this approach assumes you’re taking the fields as-is, without renaming or reshaping them. That’s a notable limitation, but the goal here is to show the core idea. Once you get that, you can tweak and customize it however you need.
let
characterFields = { "id", "name", "status" },
characterFieldsFragment =
"fragment charFields on Character {" &
Text.Combine(characterFields, " ") &
"}",
tableSchema = getTypeSchema("Character", characterFields),
getCharactersQuery =
"
query getCharactersWithPaging($pageNumber: Int) {
characters(page: $pageNumber) {
info {
next
pages
}
results {
...charFields
}
}
}
" & characterFieldsFragment,
requestFunction = (query as text, page as nullable number) =>
let
requestRecord = [
query = query,
variables = [ pageNumber = page ]
],
requestBody = Json.FromValue(requestRecord),
response =
Json.Document(
Web.Contents(
"https://rickandmortyapi.com",
[
RelativePath = "graphql",
Headers = [ #"Content-Type" = "application/json" ],
Content = requestBody
]
)
)
in
response,
callCharacters =
List.Generate(
() => [
request = requestFunction(getCharactersQuery, null),
nextPage = request[data]?[characters]?[info]?[next]?
],
each [nextPage]? <> null,
each [
request = requestFunction(getCharactersQuery, nextPage),
nextPage = [request]?[data]?[characters]?[info]?[next]?
],
each [request]?[data]?[characters]?[results]?
),
combineResults = List.Combine(callCharacters),
prepareRows =
List.Transform(
combineResults,
(r) =>
List.Transform(
characterFields,
(f) => Record.FieldOrDefault(r, f, null)
)
),
buildTable =
#table(
type table tableSchema,
prepareRows
)
in
buildTableChanges opposed to the pagination version are:
The schema is built dynamically using the getTypeSchema function.
Field names are stored in the characterFields list and reused.
Rows are created using prepareRows, which loops over characterFields.
GraphQL query uses a fragment built from characterFieldsFragment.
No custom conversions; values are pulled raw via Record.FieldOrDefault.
Enforcing type conversion for rows
Just because you build an explicitly typed schema doesn’t mean the actual row values get converted. Which makes the whole thing a bit pointless. It works fine for text, but for numbers or booleans, it's pretty inconvenient. Power Query makes a clear distinction between ascribing a type (adding metadata) and converting a value (actually transforming it). When you declare a schema, you're just telling M what the data is supposed to look like. It doesn’t validate or transform the values.
You could do conversions after building the table, but it's cleaner to handle them during row creation because it keeps everything inside the table constructor and avoids the need for post-build type transformations. To fix this, we can tweak our typeTranslation to include not just the M type, but also a basic conversion function to apply during row construction.
Here's how you modify the getTypeSchema function (everything is the same prior to the snippet):
scalarMap = [
Int = [ Type = Int64.Type, Converter = Number.From ],
Float = [ Type = type number, Converter = Number.From ],
String = [ Type = type text, Converter = Text.From ],
ID = [ Type = Int64.Type, Converter = Number.From ],
Boolean = [ Type = type logical, Converter = Logical.From ]
],
defaultEntry = [ Type = type any, Converter = (x) => x ],
getToFieldsAndFilter =
List.Select(
response[data]?[__type]?[fields]?,
each List.Contains(filterFields, [name])
),
buildTypeList =
List.Transform(
getToFieldsAndFilter,
each let
base = [type]?[name],
entry = Record.FieldOrDefault(scalarMap, base, defaultEntry)
in
[
Name = [name],
Type = entry[Type],
Converter = entry[Converter],
Optional = false
]
),
typeRecs = List.Transform(buildTypeList, each [ Type = [Type], Optional = [Optional] ]),
tableType = Type.ForRecord( Record.FromList(typeRecs, filterFields), false )
in
[ TableType = tableType, FieldDefs = buildTypeList ]What we did: we renamed our typeTranslation record to scalarMap, and for testing purposes, I temporarily changed the ID type to Int so the difference shows up clearly in the table. Then we extended each entry in the map to include a Convertor field, alongside the Type. As a result, the function now returns two things: one is tableType, which holds the schema definition (same as before), and the other is the FieldDefs, a list of fields with their associated converter functions, if any.
In the actual query where we build the table, only a few things change. Since our getTypeSchema function now returns two records, we call it once and assign the outputs to separate variables for easier access:
schemaInfo = getTypeSchema("Character", characterFields),
tableSchema = schemaInfo[TableType],
fieldDefs = schemaInfo[FieldDefs],I kept the variable name tableSchema the same as before, so no changes needed there. For the row preparation step, we slightly updated the original logic to apply conversion functions. I also added basic error handling: if the conversion fails, we just return the original raw value.
prepareRows =
List.Transform(
combineResults,
(r) =>
List.Transform(
fieldDefs,
(def) =>
let
raw = Record.FieldOrDefault(r, def[Name], null)
in
try def[Converter](raw)
otherwise raw
)
),
buildTable =
#table(
type table tableSchema,
prepareRows
)You might notice this bit: def[Converter](raw). That syntax calls a function stored inside a field of a record. Since we saved our converters as actual functions in the Converter field, we can invoke them dynamically like this.