Forum Discussion
show Average minimum max in 1 table
The first pic of my original post is the source data (pre aggregated in a csv file) over each "distance" value. So for every distance value i have 4 Measure Type. As per below pic:
What I want to show from the above is
you see i have removed the distance field and then it shows me jst 1 row against each measure type. So want to show this condensed AS IS in powerbi, somehow.
Hope it makes sense now ?
Hi apmehta Sorry it took so long to response, some unexpected events came up that took me away for a few days.
Based on what your last post has described, these are the steps you'd need to do:
Firstly we need to unpivot your data. I entered your sample data using "Enter Data" and then performed the following Power Query Code.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZI9a8MwEIb/itAcXvRpSaNp1y4NnUwGhYhimqRgktD+++okU9StdpvhOPtsntd6fMPA+1ua4mviG94f9/H8yR6mlN7Y9rpn/XRJ0xiP7Pk9HthLMPklkUvmMpAd3aILNNEwOncHQVNloQ3fbQb+FD/G0/W0lN7BEk4hEF1bGjtQswFCV/R4XoNW83UpCFVibEVuLwewx3TDUqiE96UVKdJA+HIKV04hSQ7xl7qGkGH+ZA1XR+VJgKNh1kwBMlDQGt1NQAdfkUXJbNvDUdMKzq+S3gTg29hP9Wql+gad9ZctrI48TDXmy6/J9u1f7N9x25uEe2x8g//frW/Av9n83Rc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Measure Type" = _t, #"Road Name" = _t, distance = _t, carriageway = _t, #"run-off road" = _t, #"Head on" = _t, Intersection = _t, Other = _t, Total = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Measure Type", type text}, {"Road Name", type text}, {"distance", type number}, {"carriageway", Int64.Type}, {"run-off road", type number}, {"Head on", type number}, {"Intersection", type number}, {"Other", type number}, {"Total", type number}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Measure Type", "Road Name"}, "Attribute", "Value")
in
#"Unpivoted Columns"
Hopefully that gives you an idea of how to unpivot.
Next I used a matrix visual and put the Measure Type and Road Type as Rows, Attibute as Column and Value as Value. I set the value to average, but you can choose what is approprate