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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Bahalzamon
Helper I
Helper I

Connect Things that Aren't Connected

So weird one here...

I have a couple tables i am hopeing to get some calculations on but they arent connected and unsure how to connect them.

 

Table "cyb_raquestions"

Question NumberLogical NameQuestion
1cyb_raq1Favorite Color?
2cyb_raq2Foo or Bar?

 

Table "cyb_riskassessment"

I wont make all the columns, but I have 41 columns ranging from "cyb_raq1" to "cyb_raq41". All these columns have answers to these questions through a drop down selection.

 

What I am wondering is if any of you geniuses can figure out is if there is a way to connect them or at least a way I can get some visuals of this data. Example Thoughts:

  • A count of the answers for each question
  • How many people picked Blue for cyb_raq1

 

Any help or additional information needed please let me know. 🙂

(these are obviously not the real questions, just examples lol)

1 ACCEPTED SOLUTION
samratpbi
Super User
Super User

Hi, you can unpivot your 2nd table and then join.

What I have done, created 2 tables.

samratpbi_0-1710584131184.pngsamratpbi_1-1710584166686.png

Now, for 2nd table, unpivot columns with below transformation:

let
Source = Excel.Workbook(File.Contents("C:\xxxx\xxx\xxx\data.xlsx"), null, true),
cyb_riskassessment_Sheet = Source{[Item="cyb_riskassessment",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(cyb_riskassessment_Sheet,{{"Column1", type text}, {"Column2", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"cyb_raq1", type text}, {"cyb_raq2", type text}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Logical Name"}, {"Value", "Answer"}})
in
#"Renamed Columns"

Once done, simply join 2 tables based on column Logical Name and you will get the below visual:

samratpbi_2-1710584463406.png

If this resolves your problem, then please mark it as solution to help others. Thanks

View solution in original post

1 REPLY 1
samratpbi
Super User
Super User

Hi, you can unpivot your 2nd table and then join.

What I have done, created 2 tables.

samratpbi_0-1710584131184.pngsamratpbi_1-1710584166686.png

Now, for 2nd table, unpivot columns with below transformation:

let
Source = Excel.Workbook(File.Contents("C:\xxxx\xxx\xxx\data.xlsx"), null, true),
cyb_riskassessment_Sheet = Source{[Item="cyb_riskassessment",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(cyb_riskassessment_Sheet,{{"Column1", type text}, {"Column2", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"cyb_raq1", type text}, {"cyb_raq2", type text}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Logical Name"}, {"Value", "Answer"}})
in
#"Renamed Columns"

Once done, simply join 2 tables based on column Logical Name and you will get the below visual:

samratpbi_2-1710584463406.png

If this resolves your problem, then please mark it as solution to help others. Thanks

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.