API - Getting Dropdown values into PowerBI

[abaken]

let
  Source = Json.Document(
    Web.Contents(
      // "https://api2.rev.com/table/client?select= id, name,address1&$top=10000&$filter=client.indigenous=1"


        "https://api2.rev.com/table/client?$top=10000&$select=indigenous&filter=indigenous=1"
    )
  ),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"indigenous", "extendedvalues"}, {"indigenous", "extendedvalues"})
in
    #"Expanded Column1"

Hi

I am a beginner to Power BI.

I have an API and using calls I can select fields using Power Query. However, some of the fields have type = Dropdown. I want to load the data in the Dropdown into my Data Model.

I can select and filter on the field using an ID  e.g indigenous =1 gives me all the client records that have a value 1.

My issue is that I can't bring in the value associated with the ID eg 1 = European.

What I want is a table in my Data Model that stores the IDs and the values.

How is this done?

Thank You

[BA_Pete · Accepted solution]

Hi @abaken ,

It sounds like you want to be able to bring in dimension tables via your API.

For example, you are bringing in a fact table like this already:

txnID	indigenousID	value
123	1	6
456	4	4
789	2	2

And you want to get a dimension table like this to relate to it:

indigenousID	indigenousDesc
1	European
2	Asian
3	South American
4	North American

If so, then you'll need to go back to the API documentation for your source and find the endpoint that serves this information. It will probably be an endpoint that contains the words 'dropdown', 'picklist', 'lookup' or similar to that theme.

For example, an API I use has the fact tables at relative paths like:

'crm/opportunities.api' or 'crm/sales_leads.api'

It has some complete dimension tables ready-made at relative paths like:

'crm/organisations.api' or 'crm/people.api'

And I have to get other dimensions by filtering the call to a relative paths like:

'admin/picklists.api' or 'admin/picklist_entries.api'

All of this information should be available in great detail within the API documentation, so always start there.

Pete