Creating new table with distinct rows from single column with multiple value types

[TypingQuery]

Good day! 

I am trying to do a few things with a table in order to filter by values in a single column, and also to calculate scores for the whole. 

I have a table from a form with a unique Account ID, which has rows for each question:

Account ID	Question ID	Response
21234	22159	(A Name)
21234	22160	(A Date)
21234	22161	"String"
21234	22162	yes/no/blank
21234	22163	yes/no/blank
21235	22159	(A Name)
21235	22160	(A Date)
21235	22161	"String"
21235	22162	yes/no/blank
21235	22163	yes/no/blank

For each Yes/no/blank question, I have a score table that has a unique score for each question ID marked "Yes":

Question ID	Score
22161	3
22162	5
22163	6

I have tried using related to find the score for each account ID, which works somewhat, however I am unable to use filters based on the other responses, such as question ID 22159 to find the name. When filtered by those variables, the scores show as 0 and the items disappear. 

Score =

Related('Points Table'[Point Value])

True "String" =

IF('Data'[Question ID] = 26217, [Question Response], Blank())

Since I have had no luck using measures or calculated columns effectively, I thought creating a transformed table might be easier.

What I would like to do is create a table where the unique answers are placed into distinct columns, one of which is the calculated score total for each ID,  so I can use filters to transform that data. Something like:

Account ID	Name	Date	"String"	Score
21234	Name	Date	String	Score
21235	Name	Date	String	Score

Any and all assistance would be greatly appreciated. Thank you!

[v-yiruan-msft]

HIi @TypingQuery ,

I created a sample pbix file(see the attachment), please check if that is what you want.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjI0MjZR0lEyMjI0tQTSGo4Kfom5qZpKsTookmYGEEmXxBIskoZAOrikKDMvHUPKCEhXphbr5+XrJ+Uk5mVjKDDGpcAUn7NM8TnLFLezTAk5yxSns2IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Account ID" = _t, #"Question ID" = _t, Response = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Account ID", Int64.Type}, {"Question ID", Int64.Type}, {"Response", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Account ID"},  {{"Index", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Index" = Table.ExpandTableColumn(#"Grouped Rows", "Index", {"Question ID", "Response", "Index"}, {"Question ID", "Response", "Index"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Index", "Score", each try #"Points Table"[Score]{List.PositionOf(#"Points Table"[Question ID],[Question ID])} otherwise null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each if [Index]>2 then [Score] else [Response]),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Type", each if [Index]=1 then "Name" else if [Index]=2 then "Date" else "Score"),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom2",{"Question ID", "Response", "Index", "Score"}),
    #"Grouped Rows1" = Table.Group(#"Removed Columns1", {"Account ID", "Type"}, {{"Data", each try List.Sum([Custom]) otherwise [Custom]{0} }}),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows1", List.Distinct(#"Grouped Rows1"[Type]), "Type", "Data")
in
    #"Pivoted Column"

Best Regards

[TypingQuery]

Thank you for the awesome guidance!! I will give this a try and see how I end up. 

*Edit* Unfortunately I couldn't get this to work with my data and kept receiving token syntax errors. Likely due to my limited understanding off the transformation steps converting it. 

[v-yiruan-msft]

Hi @TypingQuery ,

Could you please share the codes with the syntax error in your Advanced Editor? Later we can review it and give you a suitable solution. Thank you.

Best Regards

[lbendlin]

What I would like to do is create a table where the unique answers are placed into distinct columns

Don't do that. Power BI is not Excel.  Your data is already in a good format.  Leave the pivoting work to the matrix visual.

[TypingQuery]

Thank you for the tip! I have no experience with that particular visual so I will explore it and see if I can get it to do what I need. 

*Edit* Unfortunately I am in the same boat as before, and likely need a new measure or something to calculate score in order to use the matrix effectively as it does the same thing when I filter the page, clicking on an item to drill in and then the scores disappear.