Forum Discussion

gabereal's avatar
gabereal
Frequent Visitor
7 years ago
Solved

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?
  • dm-p's avatar
    dm-p
    7 years ago

    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);