SOLVED

How can I change the series bar chart as I would like to be?

%3CLINGO-SUB%20id%3D%22lingo-sub-1516801%22%20slang%3D%22en-US%22%3EHow%20can%20I%20change%20the%20series%20bar%20chart%20as%20I%20would%20like%20to%20be%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1516801%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20spreadsheet%20with%20a%20series%20of%20questions%20on%20the%20columns%20and%20as%20rows%20numbers%20between%201-5.%3C%2FP%3E%3CP%3EI%20would%20like%20to%20have%20a%20chart%20that%20has%20on%20the%20x-axis%20the%20questions%2C%20as%20ranges%20the%20the%20possible%20values%20(1-5%20)%20and%20as%20y-axis%20number%20of%20people%20that%20chose%20that%20value.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20was%20able%20to%20do%20is%20to%26nbsp%3B%20make%20the%20column%20bar%20chart%20and%20have%20as%20x-axis%20the%20questions%20(%20great!)%2C%20but%20as%20ranges%20I%20have%20the%20data%20time%20in%20which%20the%20answer%20were%20recorded%20(%20%3CLI-EMOJI%20id%3D%22lia_disappointed-face%22%20title%3D%22%3Adisappointed_face%3A%22%3E%3C%2FLI-EMOJI%3E%20)%20and%20as%20y-axis%20I%20have%20the%20number%20of%20people(%20great!).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20what%20I%20would%20like%20to%20have%20(%20strongly%20disagree%3D%201%2C%20disagree%20%3D2%2C%20undecided%20%3D3%2C%20etc..)%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22salvolannister_0-1594537864960.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F204791i542E62DB2333EA56%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22salvolannister_0-1594537864960.png%22%20alt%3D%22salvolannister_0-1594537864960.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20help%20%3CLI-EMOJI%20id%3D%22lia_anguished-face%22%20title%3D%22%3Aanguished_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1516801%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETraining%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1517481%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20change%20the%20series%20bar%20chart%20as%20I%20would%20like%20to%20be%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1517481%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F726016%22%20target%3D%22_blank%22%3E%40salvolannister%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20recommend%20you%20reshape%20the%20data%20to%20allow%20more%20easy%20analysis%20of%20all%20questions%20on%20the%20same%20axis.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20working%20with%20survey%20data%2C%20it%20helps%20to%20include%20a%20respondent%20ID%20in%20your%20analysis.%20So%2C%20first%20add%20a%20column%20with%20a%20unique%20ID%20for%20each%20respondent.%20Call%20it%20RespondentID%20or%20something%20like%20that.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESelect%20any%20cell%20in%20your%20data%20and%20use%20Data%26gt%3BGet%20%26amp%3B%20Transform%20Data%26gt%3BFrom%20Table%2FRange%20to%20create%20a%20query%20on%20your%20raw%20data.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20Power%20Query%20Editor%2C%20hold%20down%20Ctrl%20and%20select%20both%20RespondentID%20and%20%22Informazioni%20cronologiche%22%20by%20clicking%20on%20their%20column%20headers.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENext%2C%20use%20Transform%26gt%3BAny%20Column%26gt%3BUnpivot%20columns%26gt%3BUnpivot%20other%20columns%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22FlexYourDAta_0-1594585789658.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F204872i4CBCC81F07A29CCA%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22FlexYourDAta_0-1594585789658.png%22%20alt%3D%22FlexYourDAta_0-1594585789658.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou'll%20see%20that%20you%20now%20have%20one%20row%20per%20respondent%2Fquestion%2Fanswer.%20You%20have%20two%20columns%3A%20Attribute%20and%20Value.%20To%20make%20it%20easier%20to%20understand%2C%20right-click%20each%20and%20rename%20them%20to%20Question%20and%20Response%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22FlexYourDAta_1-1594585886685.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F204879i90BCDE62BBC03539%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22FlexYourDAta_1-1594585886685.png%22%20alt%3D%22FlexYourDAta_1-1594585886685.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%20use%20Home%26gt%3BClose%26amp%3BLoad%20to%20put%20the%20data%20back%20into%20the%20workbook.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%20you%20can%20use%20Insert%26gt%3BPivot%20Chart%2C%20then%20put%20Question%20in%20the%20Axis%20(Categories)%20area%2C%20Response%20in%20the%20Legend%20(Series)%20area%20and%20Count%20of%20RespondentID%20in%20the%20Values%20area.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22FlexYourDAta_2-1594586468804.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F204895i2E4AAC59B9C8C980%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22FlexYourDAta_2-1594586468804.png%22%20alt%3D%22FlexYourDAta_2-1594586468804.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20filtered%20out%20questions%207%2C%2055%20and%2061%20since%20they%20didn't%20follow%20the%20same%20data%20domain%20as%20the%20others%20(1-5).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20the%20data%20in%20this%20format%2C%20it%20is%20also%20trivial%20to%20calculate%20the%20average%20score%20by%20question%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22FlexYourDAta_3-1594586501942.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F204896i71692210C650632E%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22FlexYourDAta_3-1594586501942.png%22%20alt%3D%22FlexYourDAta_3-1594586501942.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eor%20even%20create%20groups%20of%20questions%20and%20calculate%20statistics%20on%20those.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20original%20data%20is%20of%20course%20still%20useful%20for%20cross-tab%20analysis.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I have spreadsheet with a series of questions on the columns and as rows numbers between 1-5.

I would like to have a chart that has on the x-axis the questions, as ranges the the possible values (1-5 ) and as y-axis number of people that chose that value.

 

What I was able to do is to  make the column bar chart and have as x-axis the questions ( great!), but as ranges I have the data time in which the answer were recorded ( ) and as y-axis I have the number of people( great!).

 

This is what I would like to have ( strongly disagree= 1, disagree =2, undecided =3, etc..)

salvolannister_0-1594537864960.png

 

Please help

1 Reply
Best Response confirmed by salvolannister (Occasional Visitor)
Solution

@salvolannister 

 

I recommend you reshape the data to allow more easy analysis of all questions on the same axis.

 

When working with survey data, it helps to include a respondent ID in your analysis. So, first add a column with a unique ID for each respondent. Call it RespondentID or something like that.

 

Select any cell in your data and use Data>Get & Transform Data>From Table/Range to create a query on your raw data.

 

In the Power Query Editor, hold down Ctrl and select both RespondentID and "Informazioni cronologiche" by clicking on their column headers.

 

Next, use Transform>Any Column>Unpivot columns>Unpivot other columns:

FlexYourDAta_0-1594585789658.png

 

You'll see that you now have one row per respondent/question/answer. You have two columns: Attribute and Value. To make it easier to understand, right-click each and rename them to Question and Response:

 

FlexYourDAta_1-1594585886685.png

 

Now use Home>Close&Load to put the data back into the workbook.

 

Now you can use Insert>Pivot Chart, then put Question in the Axis (Categories) area, Response in the Legend (Series) area and Count of RespondentID in the Values area.

 

FlexYourDAta_2-1594586468804.png

 

 

I filtered out questions 7, 55 and 61 since they didn't follow the same data domain as the others (1-5).

 

With the data in this format, it is also trivial to calculate the average score by question:

 

FlexYourDAta_3-1594586501942.png

 

or even create groups of questions and calculate statistics on those.

 

The original data is of course still useful for cross-tab analysis.