topic Re: Creating new table with distinct rows from single column with multiple value types in DAX Commands and Tips

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

TypingQuery — Thu, 26 Dec 2024 19:36:36 GMT

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!

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

lbendlin — Fri, 27 Dec 2024 02:21:31 GMT

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.

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

Anonymous — Fri, 27 Dec 2024 06:46:02 GMT

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

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

TypingQuery — Fri, 27 Dec 2024 15:11:37 GMT

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.

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

TypingQuery — Fri, 27 Dec 2024 21:32:47 GMT

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.

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

Anonymous — Mon, 30 Dec 2024 03:11:28 GMT

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