cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Best practice for accessing row data in dataView.table ?

Is there a way to access row data by column name? If not, is the index of the column always dependent on the order of the dataRoles array inside the capabilities.json?

1 ACCEPTED SOLUTION

Accepted Solutions

Thanks very much for the dataRoles - I was able to set up a quick visual with this configuration to ensure that the code below works.

I got a little carried away with this one once I started testing, because while your roles should be pretty standard, I wanted to cover situations where they may not be populated or the field data type isn't what you expect so that we can avoid issues downstream.

Fundamentally your approach is what I would do. I've taken this and added in the method I posted above, but abstract the filtering into a separate function, so we don't repeat ourselves too much when searching, and also to make sure we can handle situations where the user has not supplied a value to a particular data role. Without these checks the code will silently crash, so hopefully this provides some safety if partial data is supplied.

As you're looking to cast your values to specific types, I've added a set of conditions on each to make sure there's an index (i.e. the data role has a field in it) and then they type matches the one you want, in case a user adds an incompatible field type in there that might not be what you're expecting. Of course, this may be overkill on my part, but it may give you some ideas about how to use this stuff in other ways 😉

Anyway, here's the code:

/** We can use this to avoid repeating ourself too much, and handle situations where we don't get a 
 *  result */
    function getRoleIndex(columns: powerbi.DataViewMetadataColumn[], colName: string): number {
        /** Filter for role name in columns */
            let result = columns.filter(
                    (col) => col.roles[colName]
                );
        /** We need exactly one match. If we get that, give the index back, otherwise we don't have 
         *  a valid index */
            return result && result.length === 1
                ?   result[0].index
                :   null;
    };

/** Get our indexes by filtering on data role */
    let fromIndex = getRoleIndex(dataView.table.columns, 'from'),
        typeIndex = getRoleIndex(dataView.table.columns, 'type');

/** Process our rows */
    dataView.table.rows.forEach(
            (row) =>  {
                /** We're going to make sure we got a value when we looked for the index, and we'll do
                 *  a type check against each column before we cast to the type we want. This way, we
                 *  should be able to manage issues and debug more easily later on if needed. We do
                 *  explicit null checks because an index of 0 can evaluate to null and this is 
                 *  actually a valid case for us, so we want to make sure we avoid it */
                    let tFrom = fromIndex !== null && dataView.table.columns[fromIndex].type.dateTime
                                    ?   <Date>row[fromIndex]
                                    :   null,
                        tType = typeIndex !== null && dataView.table.columns[typeIndex].type.text
                                    ?   row[typeIndex].toString()
                                    :   null;

                /** Data handling... just added some debugging here to illustrate usage */
                    console.log('From:', tFrom, 'Type:', tType);
        });

As a little something extra, because I haven't worked with this dataViewMapping before I wanted to try this approach and map it into a simple proxy for a view model, so I set up a basic interface (which is just an array of these two fields in their final state as per your above code) directly pushing the output into this array so we don't need to declare the variables and then assign them. This may not suit your requirements but though I'd include it in case you find anything useful in there. Amended code as follows:

/** Function, as before */
    function getRoleIndex(columns: powerbi.DataViewMetadataColumn[], colName: string): number {
        /** Filter for role name in columns */
            let result = columns.filter(
                    (col) => col.roles[colName]
                );
        /** We need exactly one match. If we get that, give the index back, otherwise we don't have 
         *  a valid index */
            return result && result.length === 1
                ?   result[0].index
                :   null;
    };

/** Get our indexes by filtering on data role */
    let fromIndex = getRoleIndex(dataView.table.columns, 'from'),
        typeIndex = getRoleIndex(dataView.table.columns, 'type');

/** Interface and array to test our mapping, as a proxy for our view model */
    interface ITableData {
        from: Date,
        type: string
    };
    let tableData: ITableData[] = [];

/** Process our rows by directly inserting into the above array */
    dataView.table.rows.forEach(
        (row) => {
            tableData.push({
                from: fromIndex !== null && dataView.table.columns[fromIndex].type.dateTime
                        ?   <Date>row[fromIndex]
                        :   null,
                type: typeIndex !== null && dataView.table.columns[typeIndex].type.text
                        ?   row[typeIndex].toString()
                        :   null
            });
        }
    );

/** Quick inspection of our "view model" */
    console.log(tableData);

 

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

8 REPLIES 8
Super User II
Super User II

It might be helpful to see your capabilities.json to confirm, but you can use a .filter() function with the role name to get the column.

For example, here's the capabilities for a simple variation of the circle card visual I made for a recent presentation, which has a dataRole called measure.

If I wanted to grab the column info, then I can use:

let measureData = dataViews[0].categorical.values.filter(
c => c.source.roles['measure'] /* This is my dataRole name */
)[0] /* filter() returns an array, so I just
need to specify the first entry */

At this point I can, for example, access the measure format string by using:

measureData.source.format

You can see this in the context of the same example visual here. There's a similar piece of code in this section used to get the category value.

Your approach may vary based on your implementation (particularly with regard to your dataViewMapping) but this how to not have to depend on the order of a field for a particular dataRole. I'd probably need to see some code and get some detail of your use case to provide specific help.

I hope this helps you out!

Daniel





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Frequent Visitor

Thanks for the response dm-p.

 

I am currently using a table dataView with two dataRoles: from, type.

This is my current dataViewMapping.

"dataViewMappings": [
        {
            "table": {
                "rows": {
                    "select": [ 
                        {"for": {"in": "from"}}, 
                        {"for": {"in": "type"}}
                    ]
                }
            }
        }
    ]

I can access the values of each row with the following code 

const fromIndex: number = 0; // retrieved from the dataView.table.columns iteration
const typeIndex: number = 1; //
dataView.table.rows.forEach(row => {
  let tFrom: Date = <Date>row[fromIndex];
  let tType: string = row[typeIndex].toString();
// data handling });

 

What would be your approach to retriving the row data given my scenario?

 

 

Regards,

Gabriel 

Frequent Visitor

Thanks for the response dm-p.

 

I am currently using a table dataView with two dataRoles: from, type.

This is my current dataViewMapping.

"dataViewMappings": [
        {
            "table": {
                "rows": {
                    "select": [ 
                        {"for": {"in": "from"}}, 
                        {"for": {"in": "type"}}
                    ]
                }
            }
        }
    ]

I can access the values of each row with the following code 

const fromIndex: number = 0; // retrieved from the dataView.table.columns iteration
const typeIndex: number = 1; //
dataView.table.rows.forEach(row => {
  let tFrom: Date = <Date>row[fromIndex];
  let tType: string = row[typeIndex].toString();
// data handling });

 

What would be your approach to retriving the row data given my scenario?

 

 

Regards,

Gabriel 

Hi @gabereal 

I haven't worked with the table dataViewMapping but I'm pretty sure I can help you - are you able to provide the dataRoles from your capabilties.json too, just so that I can use them to ensure that my understanding of your visual's fields is correct? I should then be able to test my code and then provide a snippet that I'm reasonably confident with.

Cheers,

Daniel





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Frequent Visitor

@dm-p ,

 

Here are my dataRoles from my capabilities.json: 

"dataRoles": [
        {
            "displayName": "From Time",
            "name": "from",
            "kind": "Grouping"
        },
        {
            "displayName": "Type",
            "name": "type",
            "kind": "Grouping"
        }
    ],

Thanks for the help,

Gabriel

Thanks very much for the dataRoles - I was able to set up a quick visual with this configuration to ensure that the code below works.

I got a little carried away with this one once I started testing, because while your roles should be pretty standard, I wanted to cover situations where they may not be populated or the field data type isn't what you expect so that we can avoid issues downstream.

Fundamentally your approach is what I would do. I've taken this and added in the method I posted above, but abstract the filtering into a separate function, so we don't repeat ourselves too much when searching, and also to make sure we can handle situations where the user has not supplied a value to a particular data role. Without these checks the code will silently crash, so hopefully this provides some safety if partial data is supplied.

As you're looking to cast your values to specific types, I've added a set of conditions on each to make sure there's an index (i.e. the data role has a field in it) and then they type matches the one you want, in case a user adds an incompatible field type in there that might not be what you're expecting. Of course, this may be overkill on my part, but it may give you some ideas about how to use this stuff in other ways 😉

Anyway, here's the code:

/** We can use this to avoid repeating ourself too much, and handle situations where we don't get a 
 *  result */
    function getRoleIndex(columns: powerbi.DataViewMetadataColumn[], colName: string): number {
        /** Filter for role name in columns */
            let result = columns.filter(
                    (col) => col.roles[colName]
                );
        /** We need exactly one match. If we get that, give the index back, otherwise we don't have 
         *  a valid index */
            return result && result.length === 1
                ?   result[0].index
                :   null;
    };

/** Get our indexes by filtering on data role */
    let fromIndex = getRoleIndex(dataView.table.columns, 'from'),
        typeIndex = getRoleIndex(dataView.table.columns, 'type');

/** Process our rows */
    dataView.table.rows.forEach(
            (row) =>  {
                /** We're going to make sure we got a value when we looked for the index, and we'll do
                 *  a type check against each column before we cast to the type we want. This way, we
                 *  should be able to manage issues and debug more easily later on if needed. We do
                 *  explicit null checks because an index of 0 can evaluate to null and this is 
                 *  actually a valid case for us, so we want to make sure we avoid it */
                    let tFrom = fromIndex !== null && dataView.table.columns[fromIndex].type.dateTime
                                    ?   <Date>row[fromIndex]
                                    :   null,
                        tType = typeIndex !== null && dataView.table.columns[typeIndex].type.text
                                    ?   row[typeIndex].toString()
                                    :   null;

                /** Data handling... just added some debugging here to illustrate usage */
                    console.log('From:', tFrom, 'Type:', tType);
        });

As a little something extra, because I haven't worked with this dataViewMapping before I wanted to try this approach and map it into a simple proxy for a view model, so I set up a basic interface (which is just an array of these two fields in their final state as per your above code) directly pushing the output into this array so we don't need to declare the variables and then assign them. This may not suit your requirements but though I'd include it in case you find anything useful in there. Amended code as follows:

/** Function, as before */
    function getRoleIndex(columns: powerbi.DataViewMetadataColumn[], colName: string): number {
        /** Filter for role name in columns */
            let result = columns.filter(
                    (col) => col.roles[colName]
                );
        /** We need exactly one match. If we get that, give the index back, otherwise we don't have 
         *  a valid index */
            return result && result.length === 1
                ?   result[0].index
                :   null;
    };

/** Get our indexes by filtering on data role */
    let fromIndex = getRoleIndex(dataView.table.columns, 'from'),
        typeIndex = getRoleIndex(dataView.table.columns, 'type');

/** Interface and array to test our mapping, as a proxy for our view model */
    interface ITableData {
        from: Date,
        type: string
    };
    let tableData: ITableData[] = [];

/** Process our rows by directly inserting into the above array */
    dataView.table.rows.forEach(
        (row) => {
            tableData.push({
                from: fromIndex !== null && dataView.table.columns[fromIndex].type.dateTime
                        ?   <Date>row[fromIndex]
                        :   null,
                type: typeIndex !== null && dataView.table.columns[typeIndex].type.text
                        ?   row[typeIndex].toString()
                        :   null
            });
        }
    );

/** Quick inspection of our "view model" */
    console.log(tableData);

 

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Frequent Visitor

@dm-p  ,

 

I've gone ahead and implemented some of the data type validation based on your feedback.

Thanks for providing such a detailed solution. 

 

Regards,

 

Gabriel

 

 

No probs! Glad you got moving 🙂





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Kudoed Authors