Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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

Need Help
Tips and Tricks
  • Icey's avatar
    Icey
    5 years ago

    Hi Anonymous ,

     

    How about this:

     

    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"

     

     

     

    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.

2 Replies

  • AllisonKennedy's avatar
    AllisonKennedy
    Community Champion
    5 years ago
    Anonymous

    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.
  • Icey's avatar
    Icey
    Community Support
    5 years ago

    Hi Anonymous ,

     

    How about this:

     

    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"

     

     

     

    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.