Monday, July 3, 2023
Sunday, July 2, 2023
5 hacks in PowerBI
1. Edit a custom table
2. Handle decimal positions
3. Draw a variable line
4. Super impose visuals
5. Auto align KPI cards
Friday, June 30, 2023
How to highlight bad data in Power BI
Most often we run into data errors in Power BI, and very often they are due to datatype mismatches. Instances like text data appearing on numeric columns, numeric data showing up on date columns is a run-of-the-mill. Due to these type mismatches, data is blanked out in Power BI.
Original data:
In Power BI:
This situation can be better handled using Power Query. We can separate out data rows with errors and show them in a different tab. We can also highlight this error data for business to easily identify and fix them. In the next data refresh, if these errors are corrected, they will automatically disappear from this errors tab and appear in main data.
This is how Error tab will look like:
Disclaimer! this method may duplicate data a bit, all the non-text columns must be duplicated to show data that caused errors. As in my experience I can tell, you don't have to watch out for all columns. There will be limited fields you need as dates or in numeric format. I don't say this method is best, but you can use this on limited data, mostly when your data is coming from files as database will have this taken care of already.
So lets see how this can be done.
Notice how text data in non-text columns is resulting in errors. If you don't see these errors, that means data types are not defined yet. You can use "Detect Data Type" option or click on the icons next to each column names manually to set appropriate data type.
Create duplicate columns for these non-text fields and give a suffix "Org" to the column names. Make sure the data type of these duplicate columns is text.
Add an index column to the table in case you don't have any unique keys in the data, this step is optional.
Creating errors table.
Now clone the main table and name it as "Projects_Errors"
On this new table, click on "Keep Errors", make sure you don't select any column before you do this.
Now click on the "Advanced Editor" and open Power Query script.
Now change the datatypes of all error columns to Text. We do this so we can replace errors with a custom text.
Use "Replace Errors" option to replace all errors with text "#Error". You can do this column by column or use below script to do all columns at once.
After you replace errors, your data will look like this.
Here is the full script
let
Source = Projects,
#"Kept Errors" = Table.SelectRowsWithErrors(Source),
#"Changed Type" = Table.TransformColumnTypes(#"Kept Errors",{{"Target Start", type text}, {"Target Finish", type text}, {"% Comp", type text}}),
Columns = Table.ColumnNames(#"Changed Type"),
#"Replace Errors" = Table.ReplaceErrorValues(#"Changed Type", List.Transform(Columns, each {_, "#Error"}))
in
#"Replace Errors"
Now lets get to the front end.
Create a new tab in Power BI and add data from newly created errors table. Add original columns for non-text fields. Your new table visual will look like this.
Rename non-text columns to remove Org suffix
Final step! set background color for these non-text columns using error columns that we replaced with text "#Error"
And you are done!
Friday, October 29, 2010
Simple way of passing values across web pages
By using
simple java script and get method in HTML, values can be passed across web
pages.
Page 1:
Lets create a simple html page "myname.html" with two text boxes for entering name.
<html>
<head>
</head>
<body>
<form name="theform" action="showname.html" method="get">
First Name: <input type="text" name="fname">
Last Name: <input type="text" name="lname"><br>
<input type="submit" value="Show">
</form>
</body>
</html>
Here get method is used to get the values from the text boxes and pass them to the action web page "showname.html".
Page 2:
Now let’s create a second html page "showname.html" which takes the values passed by "myname.html" web page.
<html>
<head>
<script type="text/javascript">
function getValue()
{
//First, we load the URL into a variable
var url = window.location.href;
//Next, split the url by the ?
var qparts = url.split("?");
//Check that there is a querystring, return "" if not
if (qparts.length == 0) { return ""; }
//Then find the querystring, everything after the ?
var query = qparts[1];
//Split the query string into variables(separates by &s)
var vars = query.split("&");
// Initialize the value with "" as default
var value = "";
// Iterate through vars, checking each one for varname
for (i=0;i<vars.length;i++)
{
// Split the variable by =, which splits name and value
var parts = vars[i].split("=");
// Load value into variable
value = value + " " + parts[1];
}
// Convert escape code
value = unescape(value);
// Convert "+"s to " "s
value.replace(/\+/g," ");
// Return the value
return value;
}
</script>
</head>
<body >
<h1>Hello,
<script type="text/javascript">
document.write(getValue());
</script>
</h1>
</body>
</html>
In the part 2 javascript method getValue() is the show maker. It does the job of getting the values passed by "myname.html" web page.
Page 1:
Lets create a simple html page "myname.html" with two text boxes for entering name.
<html>
<head>
</head>
<body>
<form name="theform" action="showname.html" method="get">
First Name: <input type="text" name="fname">
Last Name: <input type="text" name="lname"><br>
<input type="submit" value="Show">
</form>
</body>
</html>
Here get method is used to get the values from the text boxes and pass them to the action web page "showname.html".
Page 2:
Now let’s create a second html page "showname.html" which takes the values passed by "myname.html" web page.
<html>
<head>
<script type="text/javascript">
function getValue()
{
//First, we load the URL into a variable
var url = window.location.href;
//Next, split the url by the ?
var qparts = url.split("?");
//Check that there is a querystring, return "" if not
if (qparts.length == 0) { return ""; }
//Then find the querystring, everything after the ?
var query = qparts[1];
//Split the query string into variables(separates by &s)
var vars = query.split("&");
// Initialize the value with "" as default
var value = "";
// Iterate through vars, checking each one for varname
for (i=0;i<vars.length;i++)
{
// Split the variable by =, which splits name and value
var parts = vars[i].split("=");
// Load value into variable
value = value + " " + parts[1];
}
// Convert escape code
value = unescape(value);
// Convert "+"s to " "s
value.replace(/\+/g," ");
// Return the value
return value;
}
</script>
</head>
<body >
<h1>Hello,
<script type="text/javascript">
document.write(getValue());
</script>
</h1>
</body>
</html>
In the part 2 javascript method getValue() is the show maker. It does the job of getting the values passed by "myname.html" web page.
Subscribe to:
Posts (Atom)