cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

Calculate values from a form

We have implemented a form that measures the level of stress, psicosocial risk  and some others
and I have to make some charts with that info
but I'm getting stuck with some formulas

we have 4 possible answers for each questions (Always, Usually, Sometimes, Rarely and Never) each one has a value (0,1,2,3,4 on the same order) and I need to dived them into groups and those group ino antoher group

something like this

 

EC3_0-1600457811210.png

the "ITEM" field is the number of each question

ej. "Ambiente de Trabajo" cover from question 1 to 5, and "Condiciones peligrosas" are the question 1 and 3 

So I need to count how many times per questions does the number 0 appears
the times that 1 appears etc ect

Question 1 ------------- Times
      4                                  14
      3                                   3
      1                                   3
      2                                   0

so then I cant sum the result and create a new column for "Condiciones del trabajo" and get the total       
it is really confusing

the whole table is in this link 
NOM-035 

EC3_1-1600458149230.png

 


Hope you can help me

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Hi @EC3 ,

 

How about this:

item.gif

 

let
    Source = Excel.Workbook(File.Contents("D:\Power BI\Temp Testing Case\Calculate values from a form.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Categoría", type text}, {"Dominio", type text}, {"Dimensión", type text}, {"Ítem", type any}}),
    #"Filled Down" = Table.FillDown(#"Changed Type",{"Categoría", "Dominio"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Filled Down",{{"Ítem", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type1", {{"Ítem", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Ítem"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Ítem", Int64.Type}})
in
    #"Changed Type2"

 

count.PNG

 

 

In addition, if it won't involve returning two "1" in "11", you can refer to this post: DAX function to search for multiple values in strings and count the number of times each value occur.

 

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
Super User II
Super User II

@EC3

Survey results often come in needing a bit of transformation in Power Query. I can't know without seeing the raw data, but I think you will need to do some split columns and unpivot, and possibly even split a few tables.

Are you able to share at least the first 3-5 rows of the raw data table results please? Remove sensitive data.

 


______________


Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


excelwithallison.com

Community Support
Community Support

Hi @EC3 ,

 

How about this:

item.gif

 

let
    Source = Excel.Workbook(File.Contents("D:\Power BI\Temp Testing Case\Calculate values from a form.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Categoría", type text}, {"Dominio", type text}, {"Dimensión", type text}, {"Ítem", type any}}),
    #"Filled Down" = Table.FillDown(#"Changed Type",{"Categoría", "Dominio"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Filled Down",{{"Ítem", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type1", {{"Ítem", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Ítem"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Ítem", Int64.Type}})
in
    #"Changed Type2"

 

count.PNG

 

 

In addition, if it won't involve returning two "1" in "11", you can refer to this post: DAX function to search for multiple values in strings and count the number of times each value occur.

 

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors