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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

AugPowerBI_Carousel

Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

August Carousel

Fabric Community Update - August 2024

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