The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the August 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
83 | |
73 | |
49 | |
42 |
User | Count |
---|---|
140 | |
113 | |
73 | |
64 | |
62 |