Need help: Display values

[Ad3ViLl]

Hey everyone. I need help to display some values on a chart (bar or decomposition-tree). So, I've got a list of questions that the users selected which ones were applicable and was recorded with other values. I need to display chart with the axis based on the question and the values are the count of that specific question that was recorded. There is currently no relationship between the 2 tables.

This the question list (unique index)

 

Applicable questions was recorded (Index of each applicable question, comma seperated)

[jdbuchanan71 · Accepted solution]

@Ad3ViLl 

Your best bet would be to split the second table field by comma delimiter and convert it to a single column by unpivoting the columns it splits the field into.  You can do all that in PowerQuery.

[v-shex-msft · Accepted solution]

HI @Ad3ViLl,

I agree with jdbuchanan71's point of view, you need to do some table structure transform on table 2 for further operations.

operation steps:

1. Add an index column to table 2.

Table.AddIndexColumn 
2. Split text to list.

Text.Split 

3. Expand the column who stored the above list of values to rows.

Expand column containing list of records 

After these steps, you can extract and merge two table id to create a unique id bridge table to link two tables.

Bridge =
DISTINCT (
    UNION ( ALL ( Table1[QuestionIndex] ), ALL ( Table2[QuestionIndex] ) )
)

Regards,

Xiaoxin Sheng

[jdbuchanan71 · Accepted solution]

@Ad3ViLl 

Your best bet would be to split the second table field by comma delimiter and convert it to a single column by unpivoting the columns it splits the field into.  You can do all that in PowerQuery.