Forum Discussion
Custom correlations or visuals
I'm no statistician but, following the sample, I think you're right:
- Unpivot the values for each hour (i.e. highlight Name and Date, then Unpivot Other Columns),
- Rename the values columns to reflect the variable - e.g. "Number of errors", "Total proc time", "Volume of messages"; then
- Merge the three tables on Name and Date and Time Band with your three variables then on each row, unless you want to build table relationships etc..
Then add a corrplot visual via R per the sample. Note that as of yesterday's update, you can alternately add that specific corrplot as a custom "R-powered visual" via https://app.powerbi.com/visuals/ So far though, I've found no way to customise the plot with that custom visual, rather than writing it in R.
My quick test seemed to work per below screenshot, though all the zeros for Errors didn't seem to help. As I say, I'm no statistician - good luck...
Hello Steve:
I am trying to merge per your post
Merge the three tables on Name and Date and Time Band with your three variables then on each row, unless you want to build table relationships etc..
I unpivot the columns. I used merge query and append query but that is not working. Data is not appending correctly. I wanted to append columns not rows. Any suggestion. Should I just copy and paste.
If you've unpivoted each table, you should have 3 x tables with Name, Date and Attribute as common columns and the variables you are correlating as the last column in each, per sample below for "Number of errors":
Your new query to merge those on the common columns should look something like:
let
Source = Table.NestedJoin(#"Volume of messages",{"Name", "Date", "Attribute"},#"Number of errors",{"Name", "Date", "Attribute"},"NewColumn",JoinKind.Inner),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Volume of messages", type number}}),
#"Expanded NewColumn" = Table.ExpandTableColumn(#"Changed Type", "NewColumn", {"Number of errors"}, {"Number of errors"}),
#"Merged Queries" = Table.NestedJoin(#"Expanded NewColumn",{"Name", "Date", "Attribute"},#"Total Proc Time for all messages per hour",{"Name", "Date", "Attribute"},"NewColumn",JoinKind.Inner),
#"Expanded NewColumn1" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Total proc time"}, {"Total proc time"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded NewColumn1",{{"Number of errors", "Number of errors"}, {"Total proc time", "Total proc time"}, {"Volume of messages", "Volume of messages"}})
in
#"Renamed Columns"
If you click the settings "cog" beside the first Source "Applied Step" at the right, you can see the join settings I used. I hope that works for you.