Forum Discussion
Best practice for accessing row data in dataView.table ?
- 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);
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
- gabereal7 years agoFrequent 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
- dm-p7 years agoSuper User
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);