Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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.
You are welcome. "Unpivot Other columns" changes thw wide dataset to a long one.
Please mark my original rely as Answer.
On the Format icon in the visualisation pane, there is an option to switch the orientation to horizontal.
Hi,
It is always recommended to create a Calendar Table.
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
Hi,
You may download my PBI file from here.
Hope this helps.
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.
You are welcome. If my reply helped, please mark that as Answer.
do you mind show me the Steps from the original data (wide) to what you have (long)?
Hi,
Please review all the steps shown in the Applied steps section of the Query Editor.
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...
You are welcome. "Unpivot Other columns" changes thw wide dataset to a long one.
Please mark my original rely as Answer.
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.
first issue is resolved... 2nd on the PARAMETER is not solved yet.
On the Format icon in the visualisation pane, there is an option to switch the orientation to horizontal.
Hi,
It is always recommended to create a Calendar Table.
got it, thank you.
thanks for your kind reply... I have added that new column. How do I display those months on the x-axis?
@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
User | Count |
---|---|
101 | |
90 | |
80 | |
71 | |
69 |
User | Count |
---|---|
114 | |
98 | |
97 | |
73 | |
72 |