cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
abaak Frequent Visitor
Frequent Visitor

From 3 tables to one

Hi all

 

I have a main table where all the info for the items is. With links to other tables. I need to get 3 tables in the main table as columns. Can someone help me?

The data is shown like this:

 

table: item_attribute_type

itemAttributeTypeIdName
1Cosmetic Damage
2Privacy Data
3Defect

 

Table: item_attribute_value

itemAttributeTypeIditemAttributeValueIdValue
11None
12Medium
13Heavy
24No
25Yes
36No
37Yes

 

table: item_attribute 

itemIDitemAttributeTypeIditemAttributeValueId
113
125
137
211
224
236
312
324
336
411
424
437
511
525
536
612
625
636

 

 

At this moment the data is shown in a way that it is easy to add another attribute type. But to create a visual from this is for me very hard.

I want to have the columns like the following:

I know i need to creat the names of the columns myself. But how can I get the data from cosmetic damage for item 1 in the right column. probably with a LOOKUPVALUE but I cannot figure it out anymore.

Cosmetic DamagePrivacy DataDefectitemID
HeavyYesYes1
NoneNoNo2
MediumNoNo3
NoneNoYes4
NoneYesNo5
MediumYesNo6

 

Thanks in advance!

1 ACCEPTED SOLUTION

Accepted Solutions
richbenmintz
Advisor

Re: From 3 tables to one

Hi @abaak,

 

k, the simple solution

-> create two calculated columns in item_attribute table

Type Value = RELATED('Table1'[Name]) 
Value Name = RELATED(Table2[Value]) 

-> Use the Matrix Visualisation, place Item_id on rows, Type Value on Columns and Value Name on Values and you should see the following table

related column.PNG

 

3 REPLIES 3
richbenmintz
Advisor

Re: From 3 tables to one

Hi @abaak,

 

--Update

Sorry I misunderstood the requirement, you are looking to pivot the data, this can be done with power query through a series of Pivots and Joins, the picture below show the results and the Power Query code shows how i got there

 

three tables to one.PNG

 

let
    Source = Excel.Workbook(File.Contents("C:\Users\rmintz\Downloads\Data for Comm.xlsx"), null, true),
    Table3_Table = Source{[Item="Table3",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table3_Table,{{"itemID", Int64.Type}, {"itemAttributeTypeId", Int64.Type}, {"itemAttributeValueId", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"itemAttributeTypeId"},item_attribute_type,{"itemAttributeTypeId"},"item_attribute_type",JoinKind.LeftOuter),
    #"Expanded item_attribute_type" = Table.ExpandTableColumn(#"Merged Queries", "item_attribute_type", {"Name"}, {"Name"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded item_attribute_type", List.Distinct(#"Expanded item_attribute_type"[Name]), "Name", "itemAttributeValueId"),
    #"Merged Queries1" = Table.NestedJoin(#"Pivoted Column",{"Cosmetic Damage"},item_attribute_value,{"itemAttributeValueId"},"item_attribute_value",JoinKind.LeftOuter),
    #"Expanded item_attribute_value" = Table.ExpandTableColumn(#"Merged Queries1", "item_attribute_value", {"Value"}, {"Value"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded item_attribute_value",{{"Value", "Cosmetic"}}),
    #"Merged Queries2" = Table.NestedJoin(#"Renamed Columns",{"Privacy Data"},item_attribute_value,{"itemAttributeValueId"},"item_attribute_value",JoinKind.LeftOuter),
    #"Expanded item_attribute_value1" = Table.ExpandTableColumn(#"Merged Queries2", "item_attribute_value", {"Value"}, {"Value"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Expanded item_attribute_value1",{{"Value", "Privacy"}}),
    #"Merged Queries3" = Table.NestedJoin(#"Renamed Columns1",{"Defect"},item_attribute_value,{"itemAttributeValueId"},"item_attribute_value",JoinKind.LeftOuter),
    #"Expanded item_attribute_value2" = Table.ExpandTableColumn(#"Merged Queries3", "item_attribute_value", {"Value"}, {"Value"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Expanded item_attribute_value2",{{"Value", "Defect Value"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns2",{"Cosmetic Damage", "Privacy Data", "Defect", "itemAttributeTypeId"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"itemID"}, {{"Cos", each List.Max([Cosmetic]), type text}, {"Priv", each List.Max([Privacy]), type text}, {"Def", each List.Max([Defect Value]), type text}}),
    #"Renamed Columns3" = Table.RenameColumns(#"Grouped Rows",{{"Priv", "Privacy Data"}, {"Cos", "Cosmetic Damage"}, {"Def", "Defect"}})
in
    #"Renamed Columns3"

Hope this helps,

 

Richard

 

Why don't you join the tables together and form a star schema in the visual relationships view, this is best practice? You coun also merge the data together in Power Query if you would rather deal with a single denormalized table.

 

 

Highlighted
abaak Frequent Visitor
Frequent Visitor

Re: From 3 tables to one

I just need the last table to get a visual we want. I am still a beginner in these kind of things so I could not get the merging of tables to work. There is probably an easy way of doing this. or another way to create the visual I want.

 

I want to create a column chart with the Cosmetic damage, privacy data and defect (and other in the real data) in the x-axis as levels. so you can see per attribute how many items have heavy damange or no damage or medium damage. same for privacy data.

 

I just have a hard time creating this.

 

richbenmintz
Advisor

Re: From 3 tables to one

Hi @abaak,

 

k, the simple solution

-> create two calculated columns in item_attribute table

Type Value = RELATED('Table1'[Name]) 
Value Name = RELATED(Table2[Value]) 

-> Use the Matrix Visualisation, place Item_id on rows, Type Value on Columns and Value Name on Values and you should see the following table

related column.PNG