Forum Discussion

Helper II

3 years ago

Solved

how to make a suggestion?

How do we submit a suggestion to Microsoft? Probably a long shot, but some kind of new visual that shows the same layout as the "Data View" on the canvas would be awesome. We have to create so many p...

General Comment

Martin_D

3 years ago

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'> 
      &nbsp; 
    </td> 
     <td id=tdTableDropdown style='padding: 3px; background-color: threedface; vertical-align: top; padding-bottom: 3px'>

      &nbsp; 
    </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'>

      &nbsp; 
    </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-service#export-summarized-data

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.

Martin_D

Solution Sage

3 years ago

Hi IC_SLFLORES ,

Referring to your initial question, the Ideas section is what you are looking for. just click on "Suggest an idea":

https://community.fabric.microsoft.com/t5/Custom-Visuals-Ideas/idb-p/CustomVisualsIdeas

To work around your current grouping problem with the available table visual, you can add a row number column to the table you load, then, when adding the row number column to the table visual, there is no more grouping because each row has an individual number. You can do this e.g. using the Index column function in Power Query.

You say, you have people that regularly check manually in the data view for outliers. Maybe you can automate some rule based checks e.g. in Power Query and send violations or suspicious records to a quality check table, to reduce manual effort?

Martin

IC_SLFLORES

Helper II

3 years ago

We have implemented the use of an Index to force a unique identifier, it just adds an extra step. We have a very small analytics team of 3, and many users that are involved in the QA process (200+ over all reports) so creating any tools requires a heavy load on our developers. Ultimately ours users just really want the ability to explore the data for themselves to verify quality. The table visual doesn't allow the same kind of experience for exploration as something like the data view does for developers. We often have to pull up the desktop file to explore the data with our users which signficantly slows down our deployment cycle.

Martin_D

Solution Sage

3 years ago

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.

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'> 
      &nbsp; 
    </td> 
     <td id=tdTableDropdown style='padding: 3px; background-color: threedface; vertical-align: top; padding-bottom: 3px'>

      &nbsp; 
    </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'>

      &nbsp; 
    </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.

https://learn.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-export-data?tabs=powerbi-service#export-summarized-data

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.

Forum Discussion

how to make a suggestion?

Related content

chart suggestion

chart suggestion

Need Suggestion

Need Performance Improvement Suggestions

charts margin (suggestion)