Forum Discussion

Power-CJ's avatar
Power-CJ
Helper I
2 years ago
Solved

No Filter Contex after Transforming Table in Power Query for Frequency Distribution

Hello there,

 

In my dataset, I have a set of people in rows with different sociographic attributes in the columns, like age, job, married and so on.

 

In addition to that, there are many columns with characteristic attributes. Most of the expressions range from 0 to 5, where 0 stands for no answer, and then it goes from "absolutely not" to "absolutely." These values represent the responses from a questionnaire.

 

I was building a report to analyze the frequency distribution of the characteristic attributes depending on the answers from 0 to 5.

 

Therefore, I performed a transformation in Power Query, and it worked fine. I created a new table for each characteristic to conduct the transformation for the frequency distribution. I unpivoted this table to convert columns into rows.

 

However, the filter context is not working in the report. I can't filter with the sociographic attributes. The new tables I generated use function reference by right-clicking on the original table.

 

Filter context is not working, and I can't create relationships between the tables due to a missing primary key.

 

How can I solve this problem?

 

Thank everybody for supporting me in this case

  • Power-CJ's avatar
    Power-CJ
    2 years ago

    I didnt realise the magic of power query till now

    the solution is so easy cant belive

    thanks

5 Replies

  • Hi,

    Why have you create multiple table.  There should be just one unpivoted table.

    • Power-CJ's avatar
      Power-CJ
      Helper I

      hi, thanks for your response.

      because i need the origin table like it is. the child referenes tables are only there for a visual to show the distribution of the answears in a specifition column.

       

      Also if there will be one unpivot table, i'm not able to define a realationship betwenn both

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User

        I do not see the relevance of multiple tables.  Share some data to work with and show the expected result.