From 3 tables to one

[abaak]

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

itemAttributeTypeId	Name
1	Cosmetic Damage
2	Privacy Data
3	Defect

Table: item_attribute_value

itemAttributeTypeId	itemAttributeValueId	Value
1	1	None
1	2	Medium
1	3	Heavy
2	4	No
2	5	Yes
3	6	No
3	7	Yes

table: item_attribute 

itemID	itemAttributeTypeId	itemAttributeValueId
1	1	3
1	2	5
1	3	7
2	1	1
2	2	4
2	3	6
3	1	2
3	2	4
3	3	6
4	1	1
4	2	4
4	3	7
5	1	1
5	2	5
5	3	6
6	1	2
6	2	5
6	3	6

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 Damage	Privacy Data	Defect	itemID
Heavy	Yes	Yes	1
None	No	No	2
Medium	No	No	3
None	No	Yes	4
None	Yes	No	5
Medium	Yes	No	6

Thanks in advance!

[richbenmintz]

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

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.

[abaak]

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 · Accepted solution]

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