Forum Discussion

Bahalzamon's avatar
Bahalzamon
Icon for Helper I rankHelper I
2 years ago
Solved

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)

Need Help
  • samratpbi's avatar
    samratpbi
    2 years ago

    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

1 Reply

  • samratpbi's avatar
    samratpbi
    Icon for Super User rankSuper User
    2 years ago

    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