cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
elJukes Regular Visitor
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
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"

8.png

 

Best Regards

Maggie

View solution in original post

2 REPLIES 2
Community Support Team
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"

8.png

 

Best Regards

Maggie

View solution in original post

elJukes Regular Visitor
Regular Visitor

Re: Help with table & matrix visual

Hi Maggie

 

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

 

 

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

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

MBAS 2020

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

Difinity Conference

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

Top Solution Authors
Top Kudoed Authors (Last 30 Days)