Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
elJukes
Helper I
Helper I

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
v-juanli-msft
Community Support
Community Support

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
v-juanli-msft
Community Support
Community Support

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

Hi Maggie

 

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

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.