Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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:
Any help or additional information needed please let me know. 🙂
(these are obviously not the real questions, just examples lol)
Solved! Go to 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
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
User | Count |
---|---|
117 | |
73 | |
58 | |
49 | |
48 |
User | Count |
---|---|
171 | |
122 | |
60 | |
59 | |
56 |