Forum Discussion
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
Resident RockstarHi 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