cancel
Showing results for
Did you mean:
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

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

Hope you can help me

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support

Hi @EC3 ,

``````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],
#"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"``````

In addition, if it won't involve returning two "1" in "11", you can refer to this post:

Best Regards,

Icey

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

2 REPLIES 2
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.

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

excelwithallison.com

Community Support

Hi @EC3 ,

``````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],
#"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"``````

In addition, if it won't involve returning two "1" in "11", you can refer to this post:

Best Regards,

Icey

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

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Power Platform October Community Highlights

Check out the top community contributors across all of the communities

#### 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