Basic Graph from SharePoint List

[PPStar]

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

[lbendlin · Accepted solution]

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.

[PPStar]

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?

[lbendlin · Accepted solution]

That's where the unpivoting comes in. It allows you to then do the transforms on a single column.

[PPStar]

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?

[lbendlin]

Change the column type of the Value column to a numeric type.

[PPStar]

i changed it to whole number and its still the same. 

However, by default, its is Multi line of text as the values can sometimes be text. 

Is there a workaround for that?. some content types will store values as text and the rest will store as number. - i wanted to avoid creating duplicate months to store integers and text?

[lbendlin]

Can you provide sample data, or describe the structure of the sharepoint list fields?

[PPStar]

Ok, so i have 5 content types. Each content types consumes Site Columns plus additional data. 

e.g. 

Site Columns

Title (This is  a dropdown) 

Section

Jan - Number Data Type

Feb -  Number Data Type

Mar - -  Number Data Type

..

Dec -  Number Data Type

Jan2 - Multiple Lines of Text

Feb2 - Multiple Lines of Text

Mar2 - Multiple Lines of Text

...

Dec2 -  Multiple Lines of Text

The Content Types are 

1. Content Type 1

2. Content Type 2

3. Content Type 3

4. Content Type 4

5. Content Type 5

Content Types 4 and 5 , users can enter text values for the Jan, Feb columns, so i created extra site columns and called them Jan 2, Feb 2 etc

Ideally, it would be good if i could have just Jan, Feb , Mar as the site columns, store them all as Multiple Lines of Text - which i can do in SharePoint. 

But then PowerBI gets confused with the fields that have number values, as you said in a previous post, i have to change the value to integer, which break the text columsn.

Hope that makes sense

[lbendlin]

I did a small test

[Jan 2021] is single string, [Jan 2022] multi line text.  So they both come over ok.

The next question is what you expect to do when you enconter multiple lines of text - do you want to create new rows for each, or only consider the first item?

[lbendlin · Accepted solution]

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.