Forum Discussion
Creating new table with distinct rows from single column with multiple value types
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.
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!
5 Replies
- lbendlin
Super User
What I would like to do is create a table where the unique answers are placed into distinct columnsDon't do that. Power BI is not Excel. Your data is already in a good format. Leave the pivoting work to the matrix visual.
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.
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
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.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
