Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
Proud to be a Super User!
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.
Proud to be a Super User!
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.
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
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |