Forum Discussion
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
| 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!
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
3 Replies
richbenmintzResident Rockstar
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.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
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
