From Excel to Power BI

[Anonymous]

I want to do the similar or same thing from Excel to Power BI, but am struggling with it.

In Excel, there are 2 filters, which it intelligently combines the PARAMETER with the YEAR.

PARAMETER	YEAR	JAN	FEB	MAR	APR	MAY	JUN	JUL	AUG	SEP	OCT	NOV	DEC	AVG
TS	2019	27.01	27.38	28.13	28.97	28.68	28.16	27.76	27.82	27.98	28.33	27.9	26.73	27.9
TS	2020	27.06	27.31	28.13	28.69	28.85	27.91	27.42	27.47	27.22	27.44	27.33	26.8	27.64
TS	2021	25.94	26.26	27.17	28.05	28.45	27.8	27.37	27.2	27.48	27.76	27.66	26.94	27.34
T2M	2019	26.69	26.94	27.73	28.61	28.58	27.89	27.59	27.65	27.8	27.91	27.6	26.32	27.61
T2M	2020	26.67	26.83	27.63	28.15	28.57	27.6	27.08	27.29	26.94	27.26	26.87	26.41	27.28
T2M	2021	25.53	25.81	26.74	27.76	28.13	27.57	27.16	26.83	27.18	27.52	27.33	26.56	27.01
T2MDEW	2019	24.2	23.72	23.6	24.35	24.87	24.98	24.29	24.07	23.69	23.56	24.01	24.15	24.12
T2MDEW	2020	24.13	23.87	24.21	24.5	25.2	24.8	24.26	24.62	24.19	24.37	24.3	24.27	24.4
T2MDEW	2021	23.74	23.19	24.01	24.19	24.92	24.52	24.61	24.33	24.28	24.32	24.28	24.41	24.24
T2MWET	2019	25.44	25.33	25.66	26.48	26.73	26.44	25.94	25.86	25.75	25.74	25.8	25.24	25.87
T2MWET	2020	25.4	25.35	25.92	26.33	26.88	26.2	25.68	25.96	25.56	25.82	25.58	25.34	25.83
T2MWET	2021	24.63	24.5	25.37	25.98	26.52	26.05	25.88	25.58	25.73	25.92	25.81	25.49	25.63
T2M_MAX	2019	30.9	31.34	32.35	33.63	32.36	32.6	31.08	31.44	32.58	32.49	31.88	30.12	33.63
T2M_MAX	2020	31.05	31.18	32.36	32.67	33.87	31.12	30.39	30.42	30.97	30.98	31.56	30.06	33.87
T2M_MAX	2021	29.3	31.45	31.58	31.51	31.91	30.57	30.04	30.01	32.15	31.17	31.19	30.44	32.15
T2M_MIN	2019	23.86	23	22.98	24.27	25.98	25.44	24.73	24.79	24.13	24.57	24.63	23.68	22.98
T2M_MIN	2020	23.14	23.37	23.58	24.86	25.73	25.21	24.8	24.93	24.3	25.01	24.26	23.44	23.14
T2M_MIN	2021	22.81	21.86	23.48	25.24	25.63	25.08	24.08	24.51	24.16	24.73	24.48	23.93	21.86

But I am not sure how it is possible with Power BI.

THANKS.

[Anonymous · Accepted solution]

Fantastic, this is what I am looking for, Thanks a million.. Power BI has lots of features, and I am learning all from the basic.

[Ashish_Mathur · Accepted solution]

You are welcome.  "Unpivot Other columns" changes thw wide dataset to a long one.

Please mark my original rely as Answer.

[Ashish_Mathur · Accepted solution]

On the Format icon in the visualisation pane, there is an option to switch the orientation to horizontal.

[Anonymous]

say based on the R code, which its data is imported into Power BI, is it sufficient to do the plot without additional table for the Calendar?

res = read.csv2(
paste0(
"https://power.larc.nasa.gov/api/temporal/monthly/point",
"?parameters=T2M,T2MDEW,T2MWET,TS,T2M_MAX,T2M_MIN",
"&community=RE",
"&longitude=103.9690",
"&latitude=1.3665",
"&format=CSV",
"&start=2019",
"&end=2021"
),
skip = 14,
sep = ","
)
res$ANN <- NULL

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous · Accepted solution]

Fantastic, this is what I am looking for, Thanks a million.. Power BI has lots of features, and I am learning all from the basic.

[Ashish_Mathur]

You are welcome.  If my reply helped, please mark that as Answer.

[Anonymous]

do you mind show me the Steps from the original data (wide) to what you have (long)?

[Ashish_Mathur]

Hi,

Please review all the steps shown in the Applied steps section of the Query Editor.

[Anonymous]

fantastic...you are great. Do you mind explain the step on Unpivoted Other Columns?.. I think it is something like changing from wide to long...

[Ashish_Mathur · Accepted solution]

You are welcome.  "Unpivot Other columns" changes thw wide dataset to a long one.

Please mark my original rely as Answer.

[Anonymous]

sorry.. 2 more things: I have facing difficulties:

a) How do you get the order for the x-axis on the Month name to be Jan, Feb, Mar, ... Nov,Dec.

b) How do you get the PARAMETER to be in one row? Mine give me a check boxes in vertical column.

[Anonymous]

first issue is resolved... 2nd on the PARAMETER is not solved yet.

[Ashish_Mathur · Accepted solution]

On the Format icon in the visualisation pane, there is an option to switch the orientation to horizontal.

[Anonymous]

Ashish_Mathur

is there anyway to show the visualization without the additional Calendar table?

based on the original table with the months and years, it is sufficient to do the visualization?

[Ashish_Mathur · Accepted solution]

Hi,

It is always recommended to create a Calendar Table.

[Anonymous]

got it, thank you.

[Anonymous]

thanks for your kind reply... I have added that new column. How do I display those months on the x-axis?

[amitchandak]

@Anonymous , You have to unpivot the months in power query

https://radacad.com/pivot-and-unpivot-with-power-bi

Then you have to create a new column

Parameter year =[Parameter] & " " & [Year]

use this Parameter year  on axis and Month on legend and value on values/Yaxis