Forum Discussion
Basic Graph from SharePoint List
Hi . I am a newbie to Power BI.
I have a SharePoint List which presents data as below
Content Type | Title | Year | Jan | Feb | Mar | April ....... | Dec
Content Type A | Title A | 2022 | 12 | 5 | 536 | 21
Content Type B | Title B | 2022 | xyz | test | loreum ipsum | 134
The columns Jan to Dec are stored as Multiple Lines of Text as depending on what content type is selected, a user can enter numbers or just numbers and text
What i would like is to have a graph showing the month values for all content type a only.
I have a dates table and i have created a relationship from Date from the dates table and Created By in the sharpeoint list.
when i drag the month from the spo list to the y axis value, it seems to do a count of the month and then it stacks the value
For e.g. i need the graph to have months displayed at the bottom (x axis) in order from Jan to Dec, then the the y axis to display the value.
Can someone please start me off.
Thanks
Your data comes in as a wide table with ever increasing column count. That is unfortunate and not something you want to do in Power BI. First step is to unpivot your data to make it narrow, and with fixed column count.
You probably also want to change your month column to a more future proof yearmonth column. Usually you can use a date field (for example the first day of each month) and then tie that into your calendar table.
That's where the unpivoting comes in. It allows you to then do the transforms on a single column.
9 Replies
Your data comes in as a wide table with ever increasing column count. That is unfortunate and not something you want to do in Power BI. First step is to unpivot your data to make it narrow, and with fixed column count.
You probably also want to change your month column to a more future proof yearmonth column. Usually you can use a date field (for example the first day of each month) and then tie that into your calendar table.
Your an absolute diamond, i dont think there is a need to unpivot, although it will make it look tidier, But changing my month column to a year month and then changing that to the first day of the month and tieing that to the date table would be the solution. Genius solution.
My next question is, how would i easily change the month column to Month and Year.... I can create a custom column, but i dont want to do that for every single month, can i create one new column that looks at the column title and appends the year column on it?That's where the unpivoting comes in. It allows you to then do the transforms on a single column.
You are absolutely right, i did it and it now shows my data in a better format .
However, i am trying to create a graph to show month on the x axis and the month value on the y axis.
(i created a relationship from Month Year from SPO and the Date Table).
I put the Month Year from the Date Table on the X-Axis and when i drag the Sharepoint month value, it for some reason keeps doing a count.
In a table format, my data looks like this
But when i put it on the graph, it looks like this
How can i get my graph to show the actual values opposed to doing a count?
