<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>PowerBI Can I have it in Excel by littlebigfrog.xyz</title>
<style type="text/css" media="screen">
* {
box-sizing: border-box;
}
label {
display: block;
margin-bottom: 1em;
width: 100%;
max-width: 30em;
}
textarea {
border-radius: 5px;
color: #303a3e;
height: 15em;
padding: 1em;
width: 100%;
}
input {
display: block;
border-radius: 5px;
height: 2.5em;
width: 100%;
}
textarea:invalid,
input:invalid,
input:focus:invalid {
background-color: #faa;
}
p {
margin-bottom: 2em;
}
#result {
border: 1px solid #333;
font-family: monospace;
padding: 1em;
display: none;
}
#result.isVisible {
display: inherit;
}
</style>
</head>
<body>
<h3>Power BI <i>Can I have it in Excel?</i> tool </h3>
<p>Get an Excel table connected to your dataset</p>
<form id="form">
<label>
Dataset Id or URL
<input required id="datasetId" type="text"
pattern="^(\w{8})-(\w{4})-(\w{4})-(\w{4})-(\w{12})$|^https:\/\/app.powerbi.com\/[^ ]*\/datasets\/(\w{8})-(\w{4})-(\w{4})-(\w{4})-(\w{12})[^ ]*$"
placeholder="xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx" />
</label>
<label>
Table name or DAX</br>
<textarea required id="tableName" placeholder="" value=""></textarea>
</label>
</form>
<div id="result">
<p>Download your connection file: <a id="odcResultLink" href="#" target="_blank" download="myFile.odc"></a></p>
<p>Or share this link:<br><a id="sharingLink" href="#" target="_blank"></a></p>
</div>
<div id="help">
<p>Instructions on <a href="https://littlebigfrog.xyz/can-i-have-it-in-excel-tool">littlebigfrog.xyz</a></p>
<p>Source code on <a href="https://github.com/LittleBigFrog/LittleBigFrog.github.io/blob/main/can-I-have-it-in-Excel.html">GitHub</a>
</p>
</div>
<script>
/**
* Listen for form keyup events
*/
function init(){
get('form').addEventListener('keyup', update);
update();
}
/**
* getElementById helper function
*/
function get(id) {
return document.getElementById(id);
}
/**
* Update #result & show if form fields are valid
*/
function update(event) {
// Display result if fields validate, otherwise exit
get('result').classList.toggle("isVisible", isValidInputs());
if (!isValidInputs()) return;
// Update result link text & href
const odcResultLink = get('odcResultLink');
odcResultLink.innerHTML = getResultLinkText();
odcResultLink.download = getResultLinkText();
odcResultLink.href = getOdcResultHref();
// Update githubUrl & sharing link
const url = new URL('https://littlebigfrog.github.io/can-I-have-it-in-Excel');
url.searchParams.set("datasetID", getDatasetId());
url.searchParams.set("table", getDaxQuery());
const link = get('sharingLink');
link.innerHTML = link.href = url;
}
/**
* Get value of tableName input
*/
function getTableName(){
return get('tableName').value;
}
/**
* Check if inputs validate
*/
function isValidInputs(){
return get('datasetId').checkValidity() && get('tableName').checkValidity();
}
/**
* Return the output text from the form field values
*/
function getQueryText() {
return getTableName().replace(/\n/g, 'bibi');
}
/**
* Clean & return the dataset id input value
*/
function getDatasetId() {
const result = get('datasetId').value;
if (!result.includes("http")) {
return result;
}
return result.slice(
result.indexOf("/datasets/") + "/datasets/".length
).substr(0, 36);
}
/**
* Compose the Dax query string
*/
function getDaxQuery() {
if (
getTableName()
.toLowerCase()
.includes("evaluate")
) {
return getTableName().replace(/\n/g, " ");
}
return "evaluate '" + getTableName().replace(/\n/g, " ") + "'";
}
/**
* Create text for the result link
*/
function getResultLinkText() {
if (
getTableName()
.toLowerCase()
.includes("evaluate")
) {
return 'Custom Dax table.odc';
}
return getTableName() + '.odc';
}
function getOdcResultHref() {
return 'data:attachment/text,'
+ encodeURIComponent(
`<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>Connection</title>
<xml id=docprops>
<o:DocumentProperties xmlns:o="urn:schemas-microsoft-com:office:office" xmlns="http://www.w3.org/TR/REC-html40">
<o:Name>Connection</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-${getDatasetId()};
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, 929d0ec0-7a41-4b1e-bc7c-b754a28bddcc;
Update Isolation Level=2</odc:ConnectionString>
<odc:CommandType>Default</odc:CommandType>
<odc:CommandText>
${getDaxQuery()}
</odc:CommandText>
</odc:Connection>
</odc:OfficeDataConnection>
</xml>
</head>
</html>`
);
}
// Go go go!
init();
</script>
</body>
</html>