Connect Things that Aren't Connected

[Bahalzamon]

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 Number	Logical Name	Question
1	cyb_raq1	Favorite Color?
2	cyb_raq2	Foo 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)

[samratpbi · Accepted solution]

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

What I have done, created 2 tables.

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:

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