cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

## Help with table & matrix visual

Hello

I'm having a problem with table and matrix visuals. I've been wracking my brains and have looked online at resources but couldn't come up with a solution.

I have a data table of information about people similar in format to this:

 StudentID Gender HasChar1 HasChar2 00001 F TRUE FALSE 00002 M TRUE TRUE 00003 F FALSE FALSE 00004 F TRUE TRUE 00005 M TRUE FALSE 00006 F FALSE FALSE 00007 M FALSE TRUE

What I would like to do is present the information in this data table in this way

 Characteristic Count Measure from Other Table Female 4 25% Male 3 23% Has Char1 4 12% Not Char1 3 67% Has Char 2 3 45% Not Char 2 4 18%

To do this I have duplicated the original data table, changed the values within it to more descriptive labels (M -> Male, F -> Female and so forth) and then unpivoted the table so all the characteristics are in a single column with multiple rows per person.

I then link this unpivoted table back to the original datatable so I can combine measures from other tables containing assessment information about each person.

Whilst this 'works' it somehow feels inefficient and clunky. I'm wondering if there is a simpler, more elegant but ultimately more efficient method of achieving that goal.

Any help gratefully recieved

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Help with table & matrix visual

Hi @elJukes

Which data source do you connect? do you connect live to the data source or import?

Are you able to create measures or calculated columns or relationships between tables?

If so, after transform table structure with power query, you could add the measure to the new table to indicate the assessment information from other table.

could you show me example data so i can create a measure in the new created table.

Based on my testing, some steps in your work are needed, such as "changed the values", "unpivoted the table", i don't know how did you apply these steps.

Below is my test code in the Advanced editor.

```let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXID4pCgUFcQ09En2FUpVidayQjI80VIgCmQuDFUA0Qlsg4TVKPgOkxRTUJoMMNplDlUC0wGYlYsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [StudentID = _t, Gender = _t, HasChar1 = _t, HasChar2 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"StudentID", Int64.Type}, {"Gender", type text}, {"HasChar1", type logical}, {"HasChar2", type logical}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"StudentID"}, "Attribute", "Value"),
#"Duplicated Column" = Table.DuplicateColumn(#"Unpivoted Columns", "Attribute", "Attribute - Copy"),
#"Split Column by Position" = Table.SplitColumn(#"Duplicated Column", "Attribute - Copy", Splitter.SplitTextByPositions({0, 5}, true), {"Attribute - Copy.1", "Attribute - Copy.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Attribute - Copy.1", type text}, {"Attribute - Copy.2", type text}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type1", "Custom", each if [Value] = false then "Not" else null),
#"Inserted Merged Column" = Table.AddColumn(#"Added Conditional Column", "Merged", each Text.Combine({[Custom], [#"Attribute - Copy.2"]}, " "), type text),
#"Added Conditional Column1" = Table.AddColumn(#"Inserted Merged Column", "Custom.1", each if [Value] = "F" then "Female" else if [Value] = "M" then "Male" else if [Value] = true then [Attribute] else if [Value] = false then [Merged] else null),
#"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column1",{"Attribute", "Value", "Attribute - Copy.1", "Attribute - Copy.2", "Custom", "Merged"}),
#"Grouped Rows" = Table.Group(#"Removed Columns", {"Custom.1"}, {{"Count", each Table.RowCount(_), type number}})
in
#"Grouped Rows"```

Best Regards

Maggie

2 REPLIES 2
Community Support Team

## Re: Help with table & matrix visual

Hi @elJukes

Which data source do you connect? do you connect live to the data source or import?

Are you able to create measures or calculated columns or relationships between tables?

If so, after transform table structure with power query, you could add the measure to the new table to indicate the assessment information from other table.

could you show me example data so i can create a measure in the new created table.

Based on my testing, some steps in your work are needed, such as "changed the values", "unpivoted the table", i don't know how did you apply these steps.

Below is my test code in the Advanced editor.

```let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXID4pCgUFcQ09En2FUpVidayQjI80VIgCmQuDFUA0Qlsg4TVKPgOkxRTUJoMMNplDlUC0wGYlYsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [StudentID = _t, Gender = _t, HasChar1 = _t, HasChar2 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"StudentID", Int64.Type}, {"Gender", type text}, {"HasChar1", type logical}, {"HasChar2", type logical}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"StudentID"}, "Attribute", "Value"),
#"Duplicated Column" = Table.DuplicateColumn(#"Unpivoted Columns", "Attribute", "Attribute - Copy"),
#"Split Column by Position" = Table.SplitColumn(#"Duplicated Column", "Attribute - Copy", Splitter.SplitTextByPositions({0, 5}, true), {"Attribute - Copy.1", "Attribute - Copy.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Attribute - Copy.1", type text}, {"Attribute - Copy.2", type text}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type1", "Custom", each if [Value] = false then "Not" else null),
#"Inserted Merged Column" = Table.AddColumn(#"Added Conditional Column", "Merged", each Text.Combine({[Custom], [#"Attribute - Copy.2"]}, " "), type text),
#"Added Conditional Column1" = Table.AddColumn(#"Inserted Merged Column", "Custom.1", each if [Value] = "F" then "Female" else if [Value] = "M" then "Male" else if [Value] = true then [Attribute] else if [Value] = false then [Merged] else null),
#"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column1",{"Attribute", "Value", "Attribute - Copy.1", "Attribute - Copy.2", "Custom", "Merged"}),
#"Grouped Rows" = Table.Group(#"Removed Columns", {"Custom.1"}, {{"Count", each Table.RowCount(_), type number}})
in
#"Grouped Rows"```

Best Regards

Maggie

Regular Visitor

## Re: Help with table & matrix visual

Hi Maggie

Sorry for the delay, thanks for your help it was really useful!

Announcements

#### New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

#### Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

#### Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)