Help! Don't know how to present data as graphic showing change in results for each survey completed

[samtrotter]

Hi all,

I'm a very beginning user with PowerBI. I've got a data set created in excel based of a survey that has been taken 8 times over the last 5 years (same survey). The survey displays 24 categories that can be in different orders based off the answers to the questions (ranked from 1-24). I want to be able to have the date the survey was taken on the x-axis, ranking from 1-24 on the y-axis (with 1 being furthest from the x-axis intercept), and the category for each ranked position displayed on the graph plane with a line linking each same category from each survey to show any changes visually.

I can't seem to get excel or PowerBI to play ball and I'm not sure what to do. I've tried converting the text in the table to a number (replacing a name with an assigned number). This has been slightly more successful than having workds in the table but I just don't have the knowledge or understanding to complete the task or konw what to search for to then be able to complete the task.

Thank you in advance for any and all help.

Sam

[samtrotter]

Thanks Pragati!

What I'm trying to do should look like this (but with different colours for each category):

and the data I have in different forms as I'm not sure which one is best but the original table is:

Ranking21/02/20165/03/201611/01/20175/02/20176/12/20175/03/201827/07/201826/08/2021

Position 1	Kindness	Humour	Judgement	Creativity	Creativity	Creativity	Honesty	Kindness
Position 2	Love	Love	Creativity	Kindness	Judgement	Love	Kindness	Social Intelligence
Position 3	Humour	Judgement	Humour	Judgement	Kindness	Kindness	Love	Love of Learning
Position 4	Judgement	Fairness	Kindness	Fairness	Perspective	Leadership	Judgement	Fairness
Position 5	Creativity	Leadership	Appreciation of Beauty and Excellence	Humour	Prudence	Judgement	Perspective	Judgement
Position 6	Fairness	Kindness	Love	Leadership	Love	Humour	Prudence	Appreciation of Beauty and Excellence
Position 7	Honesty	Curiosity	Curiosity	Perspective	Fairness	Fairness	Humour	Humour
Position 8	Curiosity	Creativity	Fairness	Love	Honesty	Honesty	Leadership	Perspective
Position 9	Perserverance	Honesty	Honesty	Appreciation of Beauty and Excellence	Humour	Perspective	Bravery	Leadership
Position 10	Perspective	Perspective	Perspective	Honesty	Leadership	Social Intelligence	Teamwork	Teamwork
Position 11	Social Intelligence	Social Intelligence	Social Intelligence	Prudence	Social Intelligence	Curiosity	Curiosity	Honesty
Position 12	Teamwork	Appreciation of Beauty and Excellence	Prudence	Curiosity	Appreciation of Beauty and Excellence	Teamwork	Love of Learning	Curiosity
Position 13	Leadership	Teamwork	Leadership	Social Intelligence	Curiosity	Perserverance	Perserverance	Love
Position 14	Gratitude	Perserverance	Bravery	Love of Learning	Humility	Appreciation of Beauty and Excellence	Creativity	Creativity
Position 15	Bravery	Forgiveness	Teamwork	Bravery	Gratitude	Bravery	Gratitude	Gratitude
Position 16	Prudence	Gratitude	Perserverance	Teamwork	Perserverance	Prudence	Zest	Prudence
Position 17	Hope	Hope	Love of Learning	Gratitude	Bravery	Hope	Humility	Humility
Position 18	Love of Learning	Bravery	Humility	Perserverance	Teamwork	Love of Learning	Perspective	Bravery
Position 19	Appreciation of Beauty and Excellence	Prudence	Self Regulation	Humility	Hope	Humility	Hope	Perserverance
Position 20	Zest	Love of Learning	Gratitude	Zest	Love of Learning	Gratitude	Self Regulation	Hope
Position 21	Humility	Zest	Zest	Hope	Zest	Zest	Appreciation of Beauty and Excellence	Forgiveness
Position 22	Forgiveness	Self Regulation	Hope	Forgiveness	Self Regulation	Self Regulation	Fairness	Zest
Position 23	Self Regulation	Humility	Forgiveness	Self Regulation	Forgiveness	Forgiveness	Spirituality	Self Regulation
Position 24	Spirituality	Spirituality	Spirituality	Spirituality	Spirituality	Spirituality	Forgiveness	Spirituality

[v-yiruan-msft · Accepted solution]

Hi @samtrotter ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want.

1. Unpivot these date columns in Power Query Editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rVbLUuMwEPwVV845kARYOALFa5dDatkTVA6qeDAqHMml2Nnlb/Zb9stWcZRoWpYfUJw8Hmm6e1ojJ8/Po7ley1JqlUxG49EPqVJF67UN76qVrowNvldpRitSpY2vDIlSbmT53vlypy1IHR0AF2NGNbUrD3pD/gHlTAUnd1vZ6qNeSpEn96qkPJcZqSUh0aytkWiSIbOQCU30S/JAwiipMiQ6DqBuhDRNKJadk1kXtLQ91+AkUpt4lUUbDrCdhI5B/UVRGLLG1Fut4ksSVfmeCJUm13+W1qmtT//+chfmpkpr95AdRfoVEHPa2u7eOS7O5WLMQ3UD+zcYt6vKyO1KGGMjTC0LD5JcADRnITh3n4Hs2zso8hHYwAUB0blbJLMhI3bGxNA+c8hgwqURliEQBlImR42irreWTmN3dDz6RWL1W5s3HiL5pLX2I1k2XvENbROz7wZFTVH68ENgOjjLcADG2vgScUzUOwtPg8P0n1J4hfhQhu/15CP79qN4a2xzpW0+UsJGsNmSHVuZf9Sltp8k1HUC3DfaZHaC3QVmBvktvIl41sdIdYpn3+UGY2547RGe7FTyDNLtvoYF+UfE2Xg3+zJv+yFEjrM4KkPyEB09RiDiHyhkP//stXuk/CX5SVmV13VBq83mdxlsAP/EHPnj6HF52K6Iwq0IJJ2gTIfsHk40Joe7xW8Csk4b96RFbP+2Zob9fNaSkXrWd3Z9hLge7C6ksfYLBxUWo5LjSMHXvXYIWyz+Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Ranking = _t, #"2/21/2016" = _t, #"3/5/2016" = _t, #"1/11/2017" = _t, #"2/5/2017" = _t, #"12/6/2017" = _t, #"3/5/2018" = _t, #"7/27/2018" = _t, #"8/26/2021" = _t]),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Ranking"}, "Date", "Answers"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Columns",{{"Date", type date}})
in
    #"Changed Type"

2. Create a calculated column as below

RankNum =
RIGHT (
    'Table'[Ranking],
    LEN ( 'Table'[Ranking] ) - IFERROR ( SEARCH ( " ", 'Table'[Ranking], 1, 0 ), 0 )
)

3. Create a line chart visual (Axis: Date  Legend: Answers  Values: RankNum )

Best Regards

[samtrotter]

This is awesome!! Thank you so much. I still have so much to learn.

Kind regards,

Sam

[samtrotter]

sorry that table did not work as my brain thought it would

[Pragati11]

Hi @samtrotter ,

First thing share some sample data in a file here.

Second thing - I understand you don't have much idea on what visual to use, but you can somehow create a test visual on a paper and can share here.

The reason to ask for above details is, it is hard to understand the query completly. 🙂

Thanks,

Pragati