Smart API entities often contain properties (holding an integer or string value) that represent a value from a corresponding select list. For reports, the string value from the select list is frequently preferred. For example, displaying ConstructionType = New is more useful than its integer value, as shown below.
The following steps show you how to configure select list columns using the Power Query function provided with the connector.
-
After you connect to a Smart API, the Navigator dialog box shows all available OData entity sets, as well as a special Apply Select List function.
Certain Smart APIs use the multi-tenant pattern that exposes a single entity set
at the service root, for example, Sites. In this case, you must navigate to the desired
entity set after selecting and loading the single entity set.
-
Select the Apply Select List function in the Navigator dialog box, in addition to any entity set(s) you wish to use.
Starting with the PBI Desktop August 2019 release, there is a bug that breaks select
list support. If the Navigator dialog box displays with an error as shown, follow the sub-steps below to work around
the issue:
-
Click Transform Data from the bottom of the Navigator dialog box to invoke the Power Query Editor.
-
Select the Apply Select List function from the Queries panel.
-
On the Query Settings panel, under APPLIED STEPS, click X to delete Invoked FunctionData1.
-
Remain in Power Query Editor and continue with the steps below.
-
-
If the Power Query Editor is not displayed, click Transform data from the ribbon bar to load it. There will be items in the Queries panel for the selected entity set(s) and for the Apply Select List function.
-
Select the desired entity set from Queries.
You must know which entities in the Smart API contain columns based on select lists.
See the specific Smart API $metadata document or API documentation for details.
You must select or navigate to the parent table for the entity that contains columns
based on select list value. The sample Smart API used here exposes Sites/Plants/Pipes
(that is, Pipes is a navigation property from Plants). Therefore,
-
Pipes contain columns that are based on select list values
-
Plants is the parent entity for Pipes, so you would select or navigate to Plants.
For the sample Smart API shown here, you can click Table under the Plants column header to navigate to plants from a site.
-
-
Once you select or navigate to the parent table for an entity that contains columns based on select list values, click the Add Column tab, and click Invoke Custom Function.
-
In the Invoke Custom Function dialog box, override the default New column name (if desired), for Function query, select Apply Select List, and for filterSource, select the navigation property (entity set) that contains columns based on select list values.
-
Click OK, and the new column (e.g., PipesWithSL) is added to the end of the table.
-
Click Table under the PipesWithSL column heading for the desired plant (row).
PBI navigates to a table that is a duplicate of the input (for example, Pipes) table but contains additional column(s). Columns based on select list values are now accompanied by a like-named column with "SL" appended, which displays the select list value, as shown below:
