You could provide the users that do the validation with one Excel file per dataset and one sheet per table that connects to the Power BI dataset and loads the raw data (not Pivot table) into the Excel sheets upon clicking the Refresh Data button in Excel or automatically upon opening the file. Then they can validate the data in Excel and even use any Excel functionality to support their validation. If a table has too many rows for the Excel sheet, maybe there is a data column and it's sufficient to load the last n days of new data for validation.
This solution
- does not require Premium workspaces, Pro is sufficient
- is a one time effort per dataset
- does not require changes if columns are added, removed or renamed
- only if tables are renamed, removed or added the Excel file needs to be changed as well.
- The same security applies as defined for the user using Excel when refreshing data.
To load the raw data of one dataset table you can create an odc file (a text file describing the connection for Excel) like this, and a double-click on the file loads all raw data into an Excel sheet. Then you can copy all sheets for one dataset into one common Excel file and you can serve the users with the same information as in the Power BI file. Even without redistributing copies after updating the file.
ODC file:
<html xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=Content-Type content="text/x-ms-odc; charset=utf-8">
<meta name=ProgId content=ODC.Table>
<meta name=SourceType content=OLEDB>
<title>Workspace: My Workspace Name, Dataset: My Dataset Name, Table: My Table Name</title>
<xml id=docprops><o:DocumentProperties
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns="http://www.w3.org/TR/REC-html40">
<o:Name>Workspace: My Workspace Name, Dataset: My Dataset Name, Table: My Table Name</o:Name>
</o:DocumentProperties>
</xml><xml id=msodc><odc:OfficeDataConnection
xmlns:odc="urn:schemas-microsoft-com:office:odc"
xmlns="http://www.w3.org/TR/REC-html40">
<odc:Connection odc:Type="OLEDB">
<odc:ConnectionString>Provider=MSOLAP.8;Integrated Security=ClaimsToken;Persist Security Info=True;Initial Catalog=sobe_wowvirtualserver-xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx;Data Source=pbiazure://api.powerbi.com;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error;Identity Provider=https://login.microsoftonline.com/common, https://analysis.windows.net/powerbi/api, yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy;Update Isolation Level=2</odc:ConnectionString>
<odc:CommandType>Default</odc:CommandType>
<odc:CommandText>EVALUATE 'My Table Name'</odc:CommandText>
</odc:Connection>
</odc:OfficeDataConnection>
</xml>
<style>
<!--
.ODCDataSource
{
behavior: url(dataconn.htc);
}
-->
</style>
</head>
<body onload='init()' scroll=no leftmargin=0 topmargin=0 rightmargin=0 style='border: 0px'>
<table style='border: solid 1px threedface; height: 100%; width: 100%' cellpadding=0 cellspacing=0 width='100%'>
<tr>
<td id=tdName style='font-family:arial; font-size:medium; padding: 3px; background-color: threedface'>
</td>
<td id=tdTableDropdown style='padding: 3px; background-color: threedface; vertical-align: top; padding-bottom: 3px'>
</td>
</tr>
<tr>
<td id=tdDesc colspan='2' style='border-bottom: 1px threedshadow solid; font-family: Arial; font-size: 1pt; padding: 2px; background-color: threedface'>
</td>
</tr>
<tr>
<td colspan='2' style='height: 100%; padding-bottom: 4px; border-top: 1px threedhighlight solid;'>
<div id='pt' style='height: 100%' class='ODCDataSource'></div>
</td>
</tr>
</table>
<script language='javascript'>
function init() {
var sName, sDescription;
var i, j;
try {
sName = unescape(location.href)
i = sName.lastIndexOf(".")
if (i>=0) { sName = sName.substring(1, i); }
i = sName.lastIndexOf("/")
if (i>=0) { sName = sName.substring(i+1, sName.length); }
document.title = sName;
document.getElementById("tdName").innerText = sName;
sDescription = document.getElementById("docprops").innerHTML;
i = sDescription.indexOf("escription>")
if (i>=0) { j = sDescription.indexOf("escription>", i + 11); }
if (i>=0 && j >= 0) {
j = sDescription.lastIndexOf("</", j);
if (j>=0) {
sDescription = sDescription.substring(i+11, j);
if (sDescription != "") {
document.getElementById("tdDesc").style.fontSize="x-small";
document.getElementById("tdDesc").innerHTML = sDescription;
}
}
}
}
catch(e) {
}
}
</script>
</body>
</html>
Things you need to adjust per table:
- <o:Name>Workspace: My Workspace Name, Dataset: My Dataset Name, Table: My Table Name</o:Name>
Give the connection a unique name - <odc:ConnectionString>Provider=MSOLAP.8;Integrated Security=ClaimsToken;Persist Security Info=True;Initial Catalog=sobe_wowvirtualserver-xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx;Data Source=pbiazure://api.powerbi.com;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error;Identity Provider=https://login.microsoftonline.com/common, https://analysis.windows.net/powerbi/api, yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy;Update Isolation Level=2</odc:ConnectionString>
Change the GUIDs identifying your dataset (xxx...) and the Power BI app of your tenant (yyy...) - <odc:CommandText>EVALUATE 'My Table Name'</odc:CommandText>
Change the table name per odc file.
In order to get the app GUID (yyy...) once, open a report in Power BI Service, and export the data of one visual to Excel as "summarized data" and in file format "Excel with Live Connection", and then inspect the connection string in the connection settings.
https://learn.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-export-data?tabs=powerbi-s...
You can copy the dataset GUID from the URL when opening the dataset page in Power BI service, or do one export as described above to have a template of the connection string per dataset.
On the other hand, can't the users that do the validation simply download the Power BI file from the service to do the validation? They are supposed to see all the data for validation anyway.
