Sorting and visualizing data

[helger]

1. My table looks something like this

temp1   names min right-now max

temp10   name1 6    7              13

temp11 name2  9    10          53

temp2    ....       12    14       35

temp3

temp4

Problem here is that temp is wrong, it should go from 1 to 10, but program does it other way, 1,10,11,2,3,4,5,6,7,8,9

Is there someway i could fix this? so that numbers would go from 1 to 10? (temp) If i try sorting, it just changes 1 and 9 (up or down)

2.

  Kuupäev            Temp1  Temp2   Temp3 Temp4 Temp5  Temp6   Temp7    Temp8  Temp9 Temp10 Temp11

06.Oct/2015	23°C	-21°C	5°C	8°C	5°C	6°C	5°C	-29°C	5°C	5°C	11°C
05.Oct/2015	18°C	-25°C	5°C	7°C	5°C	6°C	8°C	-28°C	4°C	5°C	8°C
04.Oct/2015	22°C	-20°C	6°C	9°C	4°C	4°C	8°C	-21°C	6°C	5°C	9°C
03.Oct/2015	24°C	-27°C	6°C	9°C	4°C	4°C	5°C	-23°C	3°C	5°C	5°C

i have this big table, 1 month data, i just picked out latest dates. Is there a way to put this big 1 month data into some kind of graph?

[mstefancik]

Or if it is possible change table names. For table names lower than 10 add prefix 0. temp01,temp02... it will order tables in a correct way.

[deldersveld]

1. The temp column is sorting as text, where 1, 10, and 11 all are ordered before 2. If the column only contains numbers for temp already, try changing the Data Type to "Whole Number", and the sorting behavior should change to 1, 2, [...] 10, 11. If your data actually has "temp1", "temp10", "temp2"; *one* method would be to use Split Column-->By Delimiter on the Transform tab and use a Custom delimiter = "temp". This will separate the word "temp" from the actual number, and you can remove the extra column created. There are other ways to parse the text too.

2. The table data would likely plot on a graph better if it were unpivoted so that the data for the separate Temp1, Temp2, etc. columns are all in one value column. On the Transform tab, highlight the various "Temp" columns and click "Unpivot Columns". This will create two new columns for "Attribute" and "Values" where Attribute will contain the former column names and Values contains the temp data. Once the data is in this format, trying plotting it.

[helger]

First part is fixed now, thanks

Second, not much, i did as u said, made attribute and Values on all of the temps, so i have like 10 values and attributes

But still putting them into graph seems to be the hard part, for me right now at least. I am trying to put them in Line chart and in Fields, i put the dates to axis, the attribute to legend and count of value to...value. But i get one straight line with the value of 1(count of value)=How to i change this to just value and not count of value?. There is no option next to it, only remove field, count(distinct) and count

[HarrisMalik]

The temperature values in our table is in form of text having °C in front of the values. Your have to transform the data first in order to use it in the graph. Some steps to transform this data:

1. Unpivot the Temp from columns to rows. Now you have three columns available i.e. Date, Attribute and value. Rename the Attribute column to somthing like Temp

2. Split the value column based on ° delimiter which will again create two columns one with real values and one with symbol "C". Make sure the column with real values is of type whole number. you can rename these columns like Value and Symbol

3. You can now do the trick to Temp column for automatic sorting(optional)

4. Load the data and hide the columns which are not needed for report

5. Now you can plot the graphs e.g. a line chart having Date in Axis, Temp in legend and Value in Values

Hope it will help you.

Regards

Harris

[helger]

Okey, i now see a good looking line chart....BUT, i can only use one temp at Legend and one value at Values. Is there a way, like almost u did, with:

Axis

Date

Legend

Temp1

Temp2

Temp3

....

Values:

Value1

Value2

Value3

...

Or do i need to have the temps and values all on separate pages?

Thanks for helping

[WillT · Accepted solution]

Hi helger. I'm not sure exactly what you want the graph to look like, but if you want something where you have a line for each name, your data should look something like this:

Date      Name   Temperature

1/1/2015  NameA  23

1/1/2015  NameB  24

1/2/2015  NameA  25

1/2/2015  NameB  24

Power BI really works with a columnar storage engine, so each value or attribute should be in its own column. You can do things like max/min/average as calculations on top of this so just storing the raw data is easiest.

Hope that helps!

[LetBloodline]

Maybe it's formatted as text so he can't do anything else? Did you check the data type?

[helger]

Yes, it's text data type, but if i change it, to decimal or whole number, the tables Value goes to "Error" Had the same thing with first part, so i had to split it( Temp) 

[deldersveld]

Did you strip the units off of the temperatures leaving only the number, or do the values still contain "°C"? Conversion from text to number would only work if the units are not present.

[LetBloodline]

If it's text it can't do anything else. You must solve that in some way. Hard to tell how without the file. Try some transformation query on query editor like adding a coulmn like Number.FromText